Finding duplicates (Excel 2003)

Topic

New Reply

Hi,

I have a spreadsheet containing hundreds of rows of data to be up-loaded to a database table. The key field in the table is Employee ID. A constraint in the table prevents duplicate Employee IDs. I will upload the data using the SQL Server tool called DTS.

What can I do in Excel (I don’t speak VBA) to discover any duplicate rows before I perform the upload? There are too many rows to rely on just scanning by eye.

Regards

Reply | Quote

Replies

Say that the EmployeeIDs are in A2:A1000.
Insert a blank column between columns A and B.
In B2, enter the formula

=COUNTIF($A$2:$A:$1000,A2)>1

and fill down to B1000. The cell in B will display TRUE if the entry in A has duplicates, otherwise FALSE.
When you’re done with the inspection, you can delete column B.

See post 394,358 or post 402,054 for a way to use Data | Validation to prevent entry of duplicate values in a column.

Reply | Quote

Hans, many thanks. You know that it works, and I am, once more, reminded of the million things to learn if only one had the time.

Thanks and regards

Reply | Quote

It will be easier to spot the duplicates if you modify Hans’ formula to:


=IF(COUNTIF($A$2:$A:$1000,A2)>1,"Duplicate","")


Reply | Quote

Thanks Legare. It does make the message much more visible.

Regards

Reply | Quote