Blog

+ Free advice and other musings

Ask for Support: Excel Tip for Finding Duplicates

by | May 26, 2007 | Tech Support for Non-Techies, Underwire Newsletter

What do you do if you suspect that you have duplicate entries in your Excel spreadsheet?

Just find them. But use a formula.

1. You need to have at least one column that holds a piece of standard data, like an email address, ID number, phone number. Something that is identical against which you can compare.

2. Sort the grid by the column with standard data. For this example, the data is in column A.

3. Create an empty column next to this standard data column. Make sure the column is formatted as General.

4. In cell B2 type this formula:

=IF(A1=A2,“Duplicate”, “Unique”)

The formula reads, “if cell A1 is equal to cell A2, then this is a duplicate record and put “duplicate” into the empty cell, if it is not a duplicate then put “unique” into the empty cell.

5. Copy and paste this formula in the whole column.

6. Then you want to change the data from formulas to values. Select the column with formulas. Go Edit > Copy. Then Edit > Paste Special.
In the Paste Special pop-up, select “Values”.

7. Now you can sort your file by the column showing Duplicate and Unique, and delete the Duplicates.

Fun stuff.

You may also like …

How to Track Marketing Campaigns in GA4

How to Track Marketing Campaigns in GA4

Holiday campaign tracking provides insights into what's working or not working. But you need to set it up. It's 5 weeks and 3 days until Black Friday, and even if your business is not located in America, this retail event kicks off the holiday shopping season around...

read more