Excel formula to make AAA-BBB equal to/consistent with BBB-AAA

[Guest]

Hi, I am trying to figure out an excel formula that would easily allow for
data in one row that is the same, except in a different order, as data in
another row to be identified and replaced, so that it is in consistent order
with "same" data in all other rows. I need this information to input in a
software program that analyzes social networks, but only recognizes agreement
between 2 data entries if they are in the same order.

For example: If I have one social group of: AMY JAY KIM in one row (in 3
columns), and I have another group of, KIM AMY LEA in another row (in 3
columns), I want to be able to acknowledge that AMY and KIM are nominated
together in both groups and I want to then list them in a consistent order,
so that I can then input them in the software program. The way the program
looks at this data is through all of the individual co-nominations within one
group. So, with the above examples, the groups would be as follows:
Group 1:
AMY-JAY
AMY-KIM
JAY-KIM

Group 2:
KIM-AMY
KIM-LEA
AMY-LEA

So, as you can see, AMY-KIM and KIM-AMY are the same co-nomination, but the
program does not interpret them as such and so I need a way to make the order
consistent, if 2 individuals are ever named together. I am sure there is a
quick and easy way to deal with this in excel, but I cannot figure it out.
Any suggestions would be greatly appreciated! Thanks!

 

[Harlan Grove]

AliceJDavidson wrote...
....

For example: If I have one social group of: AMY JAY KIM in one row (in 3
columns), and I have another group of, KIM AMY LEA in another row (in 3
columns), I want to be able to acknowledge that AMY and KIM are nominated
together in both groups and I want to then list them in a consistent order,
so that I can then input them in the software program. The way the program
looks at this data is through all of the individual co-nominations within one
group. So, with the above examples, the groups would be as follows:

Group 1:
AMY-JAY
AMY-KIM
JAY-KIM

Group 2:
KIM-AMY
KIM-LEA
AMY-LEA

So, as you can see, AMY-KIM and KIM-AMY are the same co-nomination, but the
program does not interpret them as such and so I need a way to make the order
consistent, if 2 individuals are ever named together. I am sure there is a
quick and easy way to deal with this in excel, but I cannot figure it

out.

Ensure the name pairs are always in alphabetical order. If B22
contained {"AMY","JAY","KIM"}, then in F2:H2 try the formulas

F2:
=LOOKUP(2,1/(COUNTIF($B2:$D2,"<="&$B2:$D2)=1),$B2:$D2)
&"-"&LOOKUP(2,1/(COUNTIF($B2:$D2,"<="&$B2:$D2)=2),$B2:$D2)

G2:
=LOOKUP(2,1/(COUNTIF($B2:$D2,"<="&$B2:$D2)=2),$B2:$D2)
&"-"&LOOKUP(2,1/(COUNTIF($B2:$D2,"<="&$B2:$D2)=3),$B2:$D2)

H2:
=LOOKUP(2,1/(COUNTIF($B2:$D2,"<="&$B2:$D2)=1),$B2:$D2)
&"-"&LOOKUP(2,1/(COUNTIF($B2:$D2,"<="&$B2:$D2)=3),$B2:$D2)