Duplicate / Match

[KrazyAl]

Hi

I tried multiple suggestions I found on the site, none worked. I have a list of names that have been written two different ways (I.e. First name Last name and Last Name First Name)

A1 = Bill Gates
A2 = Gates Bill

I need to find these duplicates so I can fix them. I tried the text to columns and conditional format, it will find duplicate names, however still an issue to filter and find Bill Gates/Gates Bill

Thanks for you help!

Réal

 

[iliace]

This may be pointless without knowing what you've already tried, or what you intend to do once you isolate which names are duplicates - for example, how would you determine which is the correct first/last name combination?

This may be helpful, nonetheless. Determine what the opposite scenario is, then look for it in the list. If you get a "match" (i.e. non-#N/A value), then there's a duplicate, then you can filter on that value and proceed.

Excel Workbook
	A	B	C	D	E
1	Bill Gates	Bill	Gates	Gates Bill	2
2	Gates Bill	Gates	Bill	Bill Gates	1
3	Tim Barnes	Tim	Barnes	Barnes Tim	#N/A
Sheet

 

[Akinrotimi]

Try using this formular to transpose the name on A1 on cell A3:

=IFERROR(MID(A1,FIND(" ",A1,1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)),"")

Regards

Rotimi

 

[KrazyAl]

Hello

I tried the formulas you presented me and I keep getting an increase in value (Column E).

The list I have is about 120 names and about 30 are duplicate, meaning instead of following the naming convention: First Name + Last Name they were written as Last Name + First Name. I need to highlight or mark all duplicates. Once identified I can delete the incorrect name. Both Bill Gates and Gates Bill are the same person.

Here is a copy of what I got using your formulas.

Excel Workbook
	A	B	C	D	E
1	Bill Gates	Bill	Gates	Gates Bill	6
2	Gates Bill	Gates	Bill	Bill Gates	1
3	Gates Bill	Gates	Bill	Bill *Gates	#N/A
4	Steve Jobs	Steve	Jobs	Jobs Steve	6
5	Jobs Steven	Jobs	Steven	Steven Jobs	6
6	Brad T	Brad	T	T Brad	6
Sheet1

Thanks

 

[Akinrotimi]

Pasting the code below on A3 solves the problem:

=IFERROR(MID(A1,FIND(" ",A1,1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)),"")

Regards

Rotimi

 

[iliace]

You're missing the 0 as the last argument of MATCH.

You *do* want the #N/A error - that means there isn't a duplicate. Everything that has a number (regardless of how small or big the number) that means there's a duplicate in the list.

 

[KrazyAl]

Thanks I had noticed my missing 0.

This solution will help... the N/A was me trying something else and not removing the formula

Thanks!