- last edited on
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 then
List B: Family name[space]First name
I was thinking if it was possible to lookup the values based on all the letters they contained. A way to do this might be to sort the letters in the name alphabetically for each cell and then look up the sorted values. Maybe using a text sort function e.g. TEXT([value],"abc")
List A: Williiam Flynn = afiilllmnnwy List B:Flynn William = afiilllmnnwy
Now the two columns could be compared using a lookup function.
P.s. I know in an ideal world Employee IDs would always be available but I live in the real world:-)
08-30-2016 06:37 AM
08-30-2016 07:38 AM
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.
08-30-2016 07:43 AM
08-30-2016 12:49 PM
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.
08-30-2016 02:31 PM
William, if you expect maximum 3 spaces you may use to convert string which is in A$3 in reverse order
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)))
"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.
by Jim_Houli304 on June 01, 2020
by ShawnC84 on June 01, 2020
by Mishelli on June 01, 2020
by ChristineDiorio on June 01, 2020
by cuong on May 27, 2020
by Ingeborg Hawighorst on May 13, 2020
Posted in Microsoft Ignite The Tour 2019 on February 14, 2020