[SOLVED]: concatenate 2 columns (A2+B2) and compare for duplicates

#1 (**permalink**) 7th July 2009, 05:29 PM

------ Register to get rid of these "In Post" ads! ------

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

#2 (**permalink**) 7th July 2009, 05:48 PM

------ Register to get rid of these "In Post" ads! ------

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

#3 (**permalink**) 7th July 2009, 05:50 PM

------ Register to get rid of these "In Post" ads! ------

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

#4 (**permalink**) 7th July 2009, 05:50 PM

------ Register to get rid of these "In Post" ads! ------

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

Thread Tools
Show Printable Version Email this Page Mark this thread as unsolved
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)