How to find double records in Excel file?

[vanbeugen]

Is there an easy way to detect double records (rows) in an Excel file. A trick in Excel or is there any other software to detect this?

 

[JohnOB]

This is a macro I have used before from the microsoft web site, which highlites duplicate entries in one collumn (not a whole row) so you could use this if there was one particular collumn you knew would be the same in duplicate entries, you would need to created a new macro and paste in this code

http://support.microsoft.com/?kbid=213355#kb2

Also using Excel it is possible to automaticaly delete Duplicate Rows, but maybe deleting them is not what you want to do, maybe you only want to highlite them, but the instructions are at the below URL anyway

http://office.microsoft.com/en-us/assistance/HA010346261033.aspx

If neither of these do specificaly what you want, you should try some internet searches for Excel macros which will do what you want (if it can be done).

 

[Fenrirshowl]

You may want to look at Data\Filter\Advanced Filter... which will allow you to retrieve unique records from your table.

Fen

 

[GlennUK]

How do you want them detected? By having a column showing a marker for duplicates? Or do you want them highlighted using Conditional Formatting? Or do you just want to eliminate duplicates? More info please.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TommyP]

Sort the spreadsheet by the column you'd like to check for duplicates. For this example lets say it's column A. In cell B1 type this formula: =A1=A2 If they are equal the formula returns TRUE, if not equal it returns FALSE.

If you want to delete the duplicates then copy your formulas in column B and "Paste Special as a VALUE". This then changes the formula into True and Falses. Sort by column B in descending order and your Trues are at the top. Delete them and you have removed your dupes.

Regards;
Tommy P

 

[SkipVought]

Or...

use the PivotTable Wizard

Copy the results back.

Should take about 15 seconds.

Skip,
[sub]
[red][/red]
[/sub]