Comparing Two Lists

[coloradoperkins]

I have two very large data lists with names. One list has the names as LastName, FirstName all in one field. The other list has a field for LastName and a field for FirstName. I tried doing a conditional formatting to highlight duplicates, but this did not show up anything, I assume because the first list includes both first and last names. Is there a way to get it to flag partial duplicates so it will catch the last name? I know this would be imperfect but I thought I could use it to start.

These lists are large, list one is 3500 records and list two is about 30,000. Please help! We started going through the lists manually and spent about two hours to just get through the D's.

 

[jtakw]

Hi,

Can you show some sample data, preferably using XL2BB, or at least in Table format, so helpers can copy for testing.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

22 03 12.xlsm
	A	B	C	D
1	Name		Last Name	First name
2	Doe, John		Doe	Johnny
3	Doe, Jane		Derf	Fred
4	Derf, Fred		Smith	Samantha
5	Smith, Sam		Doe	John
6
Dupe Names

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D5	Expression	=MATCH($C2&", "&$D2,$A$2:$A$5,0)	text	NO
A2:A5	Expression	=COUNTIFS(C$2:C$5,LEFT(A2,FIND(",",A2)-1),D$2:D$5,REPLACE(A2,1,FIND(" ",A2),""))	text	NO

 

[Peter_SSs]

Is this what you mean?

If it is, this has a shorter rule for column A

22 03 12.xlsm
	A	B	C	D
1	Name		Last Name	First name
2	Doe, John		Doe	Johnny
3	Doe, Jane		Derf	Fred
4	Derf, Fred		Smith	Samantha
5	Smith, Sam		Doe	John
6
Dupe Names (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D5	Expression	=MATCH($C2&", "&$D2,$A$2:$A$5,0)	text	NO
A2:A5	Expression	=ROWS(FILTER(C$2:C$5,C$2:C$5&", "&D$2:D$5=A2))	text	NO

 

[coloradoperkins]

Yes, that is what I'm working with. I'm not very experienced with formulas though so your explanation is not very clear to me. I will try to figure it out though, I appreciate your response. I've worked with excel just super-casually for a long time, but have often felt like it "should" be able to do this or that. I need to start trying to work with the formulas.

 

[Peter_SSs]

Yes, that is what I'm working with. I'm not very experienced with formulas though so your explanation is not very clear to me.

So, does that mean you would like some step-by-step instructions?

 

[coloradoperkins]

What I ended up doing was googling how to separate first and last names. Then I used flash fill to separate the first and last names into two columns. Then I was able to compare the last name columns using the find duplicate function. But I am going to start watching some videos about how to use formulas in excel. And I'm sure I will be back with another question at some point! Thank you for your help.

 

[Peter_SSs]

You're welcome.