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

1. ## Excel formula to make AAA-BBB equal to/consistent with BBB-AAA

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!

2. ## Re: Excel formula to make AAA-BBB equal to/consistent with BBB-AAA

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 B2:D2
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)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1