MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Duplicate in another column


Posted by Charles on October 30, 2001 8:19 AM

I wonder if you could help me.

I have a spreadsheet that contains two columns of vehicle registration numbers.

I want to be able to tell if a registration in the second column is *also* present in the first column.

I would like a macro that will run down the second column and if it finds a duplicate registration in the first column then put the word 'duplicate' in column 3.

(I wonder if this best solved by a formula rather than a Macro.)

Many thanks

Charles


Posted by Juan Pablo on October 30, 2001 8:24 AM

If your data starts in row 2 (That is, first column in A2 and second column in B2) then you can put this formula in C2 and drag down.

=IF(COUNTIF($A:$A,B2),"Duplicate","")

Juan Pablo

Posted by Barrie Davidson on October 30, 2001 8:25 AM

It's best solved by a formula. In column C, put a MATCH formula. This will not only tell you if you have a duplicate, it will return the location of the match. The syntax is:

=MATCH(lookup_value,lookup_array,match_type)

where lookup_value is the value to look up (column B in your case), lookup_array is where you are looking (column A). Set match_type to 0 to specify an exact match. So your formula would be something like:
=MATCH(B1,$A$1:$A$300,0)

Copy this formula down column C.

Regards,
BarrieBarrie Davidson

Posted by C on October 30, 2001 8:35 AM

Posted by Charles on October 30, 2001 8:38 AM

Thank you

Crikey, they were quick replies!
Thank you Juan and Barrie.
I am trying out your solutions now.
Thank you so much, I am *very* grateful.

Charles