Find Duplicate names listed in opposite order

[Ada33]

Hi!

I have a list of random names in column A (listed as last name first an
first name second), and then a list of random names in column B (liste
in first name first and last name second). I want the C column to retur
the duplicate names. For example, in C1, I want excel to find the nam
listed in B1 amongst all the cells in column A. If there is a match,
want it to return that name. If not, it can be blank or 0, it doesn'
matter.

I think I will have to change the names so they are both listed the sam
way (either first name first or last name first) and then use an i
formula, while maintaining the same formatting so it does actuall
detect that they are duplicates.

I would love some help

 

[Claus Busch]

Hi,

Am Fri, 23 Aug 2013 03:22:42 +0100 schrieb Ada33:

I have a list of random names in column A (listed as last name first and
first name second), and then a list of random names in column B (listed
in first name first and last name second). I want the C column to return
the duplicate names. For example, in C1, I want excel to find the name
listed in B1 amongst all the cells in column A. If there is a match, I
want it to return that name. If not, it can be blank or 0, it doesn't
matter.

if in both columns the separator is a space then try in C1:
=IF(COUNTIF($A$1:$A$100,MID(B1&" "&B1,FIND(" ",B1)+1,LEN(B1)))>0,B1,0)
and copy down


Regards
Claus B.