Duplicate records in Excel

Factual Questions
1

I have a spreadsheet with approx. 5000 entries First Name, Last Name, Address,City, State, Zip.

I merged the 4 separate sublists into the Master List

I sorted the Master List by
Last name
First Name
Address

There are a whole bunch of duplicate records out of these 5000 rows of data.

The question is there an easy way to get filter out or easily get rid of the duplicates?

2

To remove exact duplicates, Highlight the columns, go to Tools, Filter, Advance filter and make sure that unique records only is checked.

Otherwise, I would sort by lastname, and do this formula.

In the column next to “x” (see below) in the second cell type…

if(x2=x1,1,0)

replace x with the letter of the lastname column.

higlight the cell with the formula and drag it down to the bottom.

Sort by that column, and delete anything with a “1” in the cell.

3

So, any luck?

4

To: 5 time champ

I’ve been asking this question for years, and “World Eater’s” solution is the best @if a2=a1,1,0

I wish Excel had a better “Semi-Duplicate” checker tool (where 1 or more columns were dups).

If you’re working on a mailing list, I suggest using some good dedicated mailing-list software, that can intelligently check for dups (including “almost” matches, due to minor misspellings, etc). Sure saves time with over 1000 records!

5

Thank guys World Eater’s solution did the job. The Advanced filter got the number of records down to a much more manageble number to inspect.