Tuesday, July 28, 2015

Duplicate Data Revisited

The proper handling of Duplicate Data has been a traditionally troublesome issue for a great many Excel users.  The Duplicates Issue can arise for several reasons:  Combining of similar databases, updating data, entry errors, etc.

While there are ways to Quickly Remove all of you duplicate data, it may be a bit rash to do so without first getting an idea of what is being obliterated.

For example, let's say you are working with Combined and Blended Sales Department data that displays your company’s reps results by individual and month.  Using Conditional Formatting, you can quickly see which records are true duplicates.

Removing Duplicates:
Since the advent of Excel 2007 (and in subsequent versions, of course), it has become laughably easy to remove your duplicate records, and to do so with a bit of Finesse:   

1.  Select the entire database (be careful not to select any self-generating key fields)
2.  Go to the Data Tools group on the Data tab, and click Remove Duplicates
3.  Select your database or table and click OK
4.  A Remove Duplicate information box will pop up (example below) giving you options as to how you want Excel to identify “Duplicates

5.  Depending on whether you want to eliminate all duplicates by Month, Rep, and/or Sales (in this example), you will then indicate which Columns to choose with the checkboxes (Note: Choosing all of the columns assures an exact duplicate by all of your data criteria).
As with anything in life, (Excel or otherwise), once you know how to do something, you will likely wonder why it ever was a problem to begin with.  Once you know how, Removing Duplicates is a Snap!  Give it a try…

 

No comments: