Duplicates in excel

Wearb

I have two columns of car reg numbers. Some of the columns will have the same numbers. I want to compare the two columns and highlight the duplicates. I then want to delete the duplicates and be left with only the unique numbers.

I have been trying to do this using the countif function, but I am not very good with it. Can any help me with this.

btw I posted this in maths also.

CasaBonita

If you have Excel 2007 or 2010 then you can use the Remove Duplicates function. Highlight the column that contains your data and select the 'Data' tab on the ribbon. Then select the 'Remove Duplicates' button. This will delete any cells that contain duplicates (it doesn't delete whole rows).

Wearb

Thanks for the reply. I have Excel 2003. Any ideas on how to do it with that?

TechnoFreek

You could put all the numbers in one column with a header field called car-reg or similar.
Stick a pivot on this to show each car reg number and the number of occurrences.

The pivot will only show unique car reg numbers and not duplicates.

Wearb

Thanks TF. I have to read up on using a pivot table. I remember following a "how to" in a magazine years ago, but I have forgotten about them completely.

tommycahir

The code in post #8 on this thread should do exactly what you are looking for

http://www.mrexcel.com/forum/showthread.php?t=334541

But if not here are some other solutions:
Here's a couple of macro based solutions for excel 2003

http://www.techonthenet.com/excel/macros/unique_col.php
http://www.techonthenet.com/excel/macros/test_dups1.php
http://www.techonthenet.com/excel/macros/test_dups6.php
http://www.techonthenet.com/excel/macros/test_dups2.php

Here's a Microsoft version using if and match functions

http://support.microsoft.com/kb/213367

Wearb

tommychair; I have managed to eventually get the script (#8) to work. I have tried so many things, that I am not sure how to repeat it. However I now have my duplicates highlighted.
Is there a way to sort (group) the highlighted numbers (I am only interested in the ones in column togther so that I may delete them and be left with only the numbers that doesnt have duplicates?

tommycahir

there are a couple of options depending on how much data you have and how often you will be repeating this action in the future
1) Use the countif function in a new column and then filter on that column
2) Edit the macro to delete the duplicate cells instead of just highlighting them.
3) Use the option in this link

Wearb

what do I put in the criteria field to ask it to pick the red text?

tommycahir

apologies, If you leave the criteria blank and ensure to check the box unique records only it should filter the list down to a list of unique values with no highlighted duplicates, As per the below screenshot


if you want to have the unique values on a separate sheet then choose the corresponding options in the dialog box for "copy to another location" and the select the cell where you want the unique list in the "copy to" entry.

The VBA code in my previous post only highlighted the duplicates across the two columns with a specific colour.

Wearb

Thanks for your help. I found this "ConFormats" macro and it does exactly what I need. I will play around with the information I have gotten from this thread to try and understand what is happening. Thank you all.

Wearb

Sorry not conformats but "ListCompare" was the one I found useful.