duplicate records (excel 2000)

Topic

New Reply

I have a spreadsheet that has a column with numbers – say 12345. There are duplicate numbers in this column. I am manually deleting the duplicate rows for the duplicate numbers. Is there any way to automatically delete records that have duplicates in this column?

Reply | Quote

Replies

Short of writing a macro, I haven’t found an easy way to do this.

Anyone know of an easy way to do this?

Reply | Quote

Off the top of my head you have at least 2 ways.

1. Assume data starts in A1. Sort by column A. In B2 put the formula =IF(A2=A1,”DUP”,””). Copy this down as far as required & then paste value the results in place. You can then sort by column B and eliminate the duplicates.

2. Data – Filter – Advanced Filter. It should be easy enough to follow from there

Reply | Quote

This spreadsheet has multiple columns and when I get rid the the duplicate numbers that are the in the group number column, I am importing this into an access database. The spreadsheet has over 5000 records so if I could delete these records without having to do it manually, it would save a lot of time. I tried to use the advanced filter but I wasn’t having any luck. I highlighted the whole spreadsheet and then for the criteria, I used the column that had the duplicates. I checked Unique records only. It got rid of everything. I wasn’t sure if this did work, I could import what was filtered to access or would the whole spreadsheet import.

Reply | Quote

1)Advanced filter – Unique records

another way:
2)Add a column (I assumed you are looking in COl A for Dups and you start in A2) and copy this.

=ISERROR(MATCH(A2,$A$1:A1,0))

This will be TRUE for unique values and False for dups. Autofilter on False and delete the rows

Steve

Reply | Quote

Thank you – that worked perfectly.

Reply | Quote

If you have to do this often, the code in This Post might make it easier.

Reply | Quote