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
