Forum Discussion

William Flynn's avatar
William Flynn
Copper Contributor
Aug 30, 2016

looking up names in seperate excel lists

I work in HR and I frequently have to cross reference lists with names of Employees. Often the names are written in a different order from list to list i.e. List A: First name[space]Family name  the...
Formulas & Functions
William Flynn's avatar
William Flynn
Copper Contributor
Aug 30, 2016

Hi Sergei,

Thanks but I couldn't get your formula to work. You said to put the formula in D3 but there was also a reference to D3 in the formula so I got a circular reference.

You also appear to have a column that gives the location of the value in the "to be compared" column. How can this be achieved when you have hundreds of names that are constructed differently (just the problem I am trying to resolve).

Probably just me being a bit slow on the pick up so I apologise.

kind regards,

Williiam

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 30, 2016
Without Rich Editor can't add the sample, i'll send you in-private message bit later. If it works, never tried here.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 30, 2016

    Fantastic, editor appeared back. Anyway, i've already sent the link with in-private message.

    • William Flynn's avatar
      William Flynn
      Copper Contributor
      Aug 30, 2016

      Hi Sergei,

       

      Well it appears to work... I actually make a similiar (but not the same) formula but it always runs into trouble with double Barrell names like John Edward Smith or Edward Van Emeren. Yours seems to cope with those. I hope to try it tomorrow on some real lists.

      I usually use =mid(A3;Find(" ";A3)+1;45)&" "&left(a3;Find(" ";A3)-1) but as I said trips on double barrel names.

      I will let you know how it goes.

      Thanks,

      Willam

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 30, 2016

        William, if you expect maximum 3 spaces you may use to convert string which is in A$3 in reverse order

        =TRIM(
        TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",LEN($A3))), (4-1)*LEN($A3)+1, LEN($A3))) & " " &
        TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",LEN($A3))), (3-1)*LEN($A3)+1, LEN($A3))) & " " &
        TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",LEN($A3))), (2-1)*LEN($A3)+1, LEN($A3))) & " " &
        TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",LEN($A3))), (1-1)*LEN($A3)+1, LEN($A3)))
        )

        It converts

        "one two" => "two one" and

        "one two three" => "three two one", etc. More max possible spaces - add more TRIM(...) lines. The only difference is (N-1) in the middle of formula.

        As everything in Excel above could be done by few different ways.

        Perhaps entire your task much easier to solve with use of Power Query, but that's fully different story.

         

Resources