+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    AliceJDavidson
    Guest

    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. #2
    Harlan Grove
    Guest

    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)


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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