# Duplicate records in Excel

**URL:** <https://boards.straightdope.com/t/duplicate-records-in-excel/172224>\
**Category:** Factual Questions\
**Created:** [April 30, 2003, 8:56pm UTC](https://boards.straightdope.com/t/duplicate-records-in-excel/172224 "2003-04-30T20:56:03Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![5\_time\_champ](https://avatars.discourse-cdn.com/v4/letter/5/278dde/32.png) [@5\_time\_champ](https://boards.straightdope.com/u/5_time_champ)\
**Post date:** [April 30, 2003, 8:56pm UTC](https://boards.straightdope.com/t/duplicate-records-in-excel/172224/1 "2003-04-30T20:56:03Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![World\_Eater](https://avatars.discourse-cdn.com/v4/letter/w/f04885/32.png) [@World\_Eater](https://boards.straightdope.com/u/World_Eater)\
**Post date:** [April 30, 2003, 9:26pm UTC](https://boards.straightdope.com/t/duplicate-records-in-excel/172224/2 "2003-04-30T21:26:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![World\_Eater](https://avatars.discourse-cdn.com/v4/letter/w/f04885/32.png) [@World\_Eater](https://boards.straightdope.com/u/World_Eater)\
**Post date:** [May 1, 2003, 1:49pm UTC](https://boards.straightdope.com/t/duplicate-records-in-excel/172224/3 "2003-05-01T13:49:56Z")

</div>

So, any luck?

---

<div class="post-metadata">

**Author:** ![bradwalt](https://avatars.discourse-cdn.com/v4/letter/b/858c86/32.png) [@bradwalt](https://boards.straightdope.com/u/bradwalt)\
**Post date:** [May 1, 2003, 1:57pm UTC](https://boards.straightdope.com/t/duplicate-records-in-excel/172224/4 "2003-05-01T13:57:01Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![5\_time\_champ](https://avatars.discourse-cdn.com/v4/letter/5/278dde/32.png) [@5\_time\_champ](https://boards.straightdope.com/u/5_time_champ)\
**Post date:** [May 1, 2003, 2:15pm UTC](https://boards.straightdope.com/t/duplicate-records-in-excel/172224/5 "2003-05-01T14:15:33Z")

</div>

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.