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


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.


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:


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:

Copy this formula down column C.

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.