Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
AliceJDavidson
 
Posts: n/a
Default 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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I make a 3-dimensional plot in Excel? John W. Mordosky Charts and Charting in Excel 6 April 3rd 23 06:58 PM
How to stop excel from plotting a "blank" cell with formula as zer tiw Excel Discussion (Misc queries) 5 April 21st 05 08:38 PM
Formula Integrity Not Preserved During Sort in Excel 2000 Kevin Excel Discussion (Misc queries) 1 April 15th 05 10:26 PM
Make Change Case in Excel a format rather than formula Kevin Excel Worksheet Functions 1 March 18th 05 09:53 PM
How do I make a name formula auto update in Excel Sammy Soup Excel Worksheet Functions 3 December 30th 04 06:18 PM


All times are GMT +1. The time now is 12:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"