concatenate 2 columns (A2+B2) and compare for duplicates

habelow1 · External Since: Jul 07, 2009 Posts: 1

I have 2 columns (First Name, Last Name) that I need to see if there are any
duplicates in the spreadsheet, and to flag them.
John Smith <----Duplicate
Jane Smith
John H Smith
John Smith <---- Duplicate

Thanks

Luke M · External Since: Jan 08, 2009 Posts: 224

Easiest with 2 helper columns.
First helper (column C):
=A2&B2
Second helper:
=IF(COUNTIF(C:C,C2)>1,"DUPLICATE","")

Copy down as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"habelow1" wrote:

> I have 2 columns (First Name, Last Name) that I need to see if there are any
> duplicates in the spreadsheet, and to flag them.
> John Smith <----Duplicate
> Jane Smith
> John H Smith
> John Smith <---- Duplicate
>
> Thanks

Jacob Skaria · External Since: Mar 04, 2009 Posts: 492

Try the below formula in C1; and copy that down as required

=IF(SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100=B1))>1,"Duplicate","")

If this post helps click Yes
---------------
Jacob Skaria

"habelow1" wrote:

> I have 2 columns (First Name, Last Name) that I need to see if there are any
> duplicates in the spreadsheet, and to flag them.
> John Smith <----Duplicate
> Jane Smith
> John H Smith
> John Smith <---- Duplicate
>
> Thanks

Steven · External Since: Jan 19, 2009 Posts: 3

In two steps

Concatenate cells A and B in column C through formula Concatenate
(A1,B1)

and drag down

In column D (the flag column)

=IF(COUNTIF(C:C,C1)>1,"duplicate","")

and drag down