Finding Duplicates using multiple columns

[RompStar]

I have columns:

D: First Name
F: Last Name
H: Address
J: City
K: State
L: ZIP

I need a formula or a way to find duplicates using all of these columns, so I have a sheet with 20,000 rows, if any row for all those columns has exact values, then ID them as a Duplicate in a column or something like that, please help!

Thank you.

 

[Yard]

This in M1:

=D1&F1&H1&J1&K1&L1

and copied down.

This will return a concatenated string.

Then in N1:

=COUNTIF(M$1:M$20000,M1)

and copied down will return the number of occurrences of the concatenated string.

HTH

 

[RompStar]

Thanks!!!!!!!!!!!!!!!!!!!!!

You da man, thank you again.

 

[RompStar]

Great this works, so 1 is single, I got some 2's and some 3's and I got one that is 41 times repeating, I think this one might be fraud :- )

Ok, so for the duplicates I need a way to pick the first row, maybe I can do another helper column that would pick the first row of any duplicates, so if it I select 2 instances, I would like to put a 1 into a helper column, and then with autofilter I can select them and copy them to a new sheet.

Thanks for all the help!

 

[RompStar]

Nevermind :- ) I just ended up using Advanced Filter, Unique records on the field that has all the columns merged.

 

[Peter_SSs]

Could you use Advanced Filter?

1. Select the whole data area, including headings.

2. Data|Filter|Advanced Filter...|Copy to another location|Copy to: Z1 (for example)|Unique records only|OK

3. Copy the range starting in Z1 to your other sheet.

Edit: Looks like you discovered Advanced Filter while I was composing my reply.

However, I think you should be able to use it directly on your original data without having to have those 20,000+ additional formulas concatenating the columns.

 

[RompStar]

Thanks for all the help. The advanced filter does the trick, but the formulas are useful too, because it allows me to count how many times someone is duplicated, I counted one party duplicating over 40 times, so that's a problem that needs to be addressed the way addresses are verified for customers with Experian.

So it's good to know how many times someone repeats.

Thanks!