Duplicate records in Excel

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?

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.

So, any luck?

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!

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.