DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download free 15-day trial | Purchase | FAQ | Contact

Find & Remove Duplicates - Dedupe Excel Tables / Lists

  1. Match two tables (lists), compare by columns, find or exclude the matched

  2. Filter duplicates within one table (list), by key columns or more complex rules

  3. Extract unique values from a random range or areas

Find & Remove Duplicates ( dedupe ) - Example #1

I have 2 lists of emails. How to filter/subtract from List1 the emails that are in List2?
    Find & Remove Duplicates - Dedupe Excel Table List
    Practice file - dedupe-email-list-demo.xls (16k)

  1. Go to List1. Click only 1 cell, then invoke "Complex Filter->Match Tables...", a wizard will start. (Note that this command is the same as "Table->Match Tables..." or "Unique->Match Tables...")

    Find & Remove Duplicates - Dedupe Excel Table List

  2. Wizard Step 1 - List1 is automatically selected.

    Find & Remove Duplicates - Dedupe Excel Table List

  3. Wizard Step 2 - Select List2.
    • Click into the range selector
    • Then you can go to the List2 sheet or area, click only 1 cell in List2
    • Click "Select Table(List)" button, to autoselect List2

    Find & Remove Duplicates - Dedupe Excel Table List

    Find & Remove Duplicates - Dedupe Excel Table List

    Find & Remove Duplicates - Dedupe Excel Table List

  4. Wizard Step 3 - Use the dropdown to select the pair of columns to match. Our case only needs one pair. To add more pairs, click 'Add Criteria'.

    Find & Remove Duplicates - Dedupe Excel Table List

  5. Wizard Step 4 - Select what to show in result

    In our case, we want to exclude from List1 the rows that are matched in List2, therefore, we choose "Hide matched rows". Note that there is also a third option - "Count Matches", which only counts the number of matched rows in List2. Click 'Finish' to match.

    Find & Remove Duplicates - Dedupe Excel Table List

  6. Match result
    • Matched rows are hidden beneath the "+". Only unmatched ( unique ) rows are shown. Duplicates are now hidden. You can click on the "+" to expand and show them.
    • Note that you can use "DigDB->Invert Filter" to toggle between matched and unmatched easily.

    Find & Remove Duplicates - Dedupe Excel Table List

    Hidden beneath the '+' are the matched rows (duplicates). You can expand '+' or '2' to to see what they are. Click the '-' or '1' to contract.

    Find & Remove Duplicates - Dedupe Excel Table List

  7. Extract result
    • Make sure the '+' is NOT expanded. Click "Extract Result->Copy Visible Rows to New Sheet", a new sheet will be created, and match result will be copied over.

    Find & Remove Duplicates - Dedupe Excel Table List

Find & Remove Duplicates ( dedupe ) - Example #2

I have 2 lists of mailing addresses. How to filter/subtract from List1 the addresses that are in List2? (* The uniqueness of a mailing address is determined by 3 columns "Street1", "Street2", "ZipCode")

    Find & Remove Duplicates - Dedupe Excel Table List
    Practice file - dedupe-mail-list-demo.xls (16k)

    This is pretty much the same as Example #1. Except in Wizard Step 3, when you select the column pairs to match, you need 3 pairs of columns. You can use the 'Add Criteria' button to add more pairs.

    Find & Remove Duplicates - Dedupe Excel Table List

Find & Remove Duplicates ( dedupe ) - Example #3

I have a list of emails. How to extract the uniques, and count the number of duplicates for each unique email?
  1. Go to your list. Click only 1 cell, then invoke "Complex Filter->Filter Uniques by Group..." (note that this command is the same as "Table->Filter Uniques by Group..." and "Unique->Filter Uniques by Group...")

    Find & Remove Duplicates - Dedupe Excel Table List
    Practice file - dedupe-email-list-demo.xls (16k)

    The list will be automatically selected, and a pop-up window will show.

  2. Select the "Email" column from the dropdown box.
    • Note that DigDB defaults to "Use All Fields". In our case, we are looking for uniques in "Email" column, therefore "Email" should be selected. You can also use 'Add Field' button to dedupe by more than one field (next demo).
    • Uncheck the "Key Field(s) is Case Sensitive" checkbox, because email addresses are case insensitive, i.e. "abc@digdb.com" is the same as "ABC@DigDB.com".

    Find & Remove Duplicates - Dedupe Excel Table List

    Find & Remove Duplicates - Dedupe Excel Table List

  3. Filter result is shown
    • Only unique rows are shown. If a row has duplicates, a '+' will appear immediately below the row, and the duplicates will hidden under the "+". You can click on the "+" to show the duplicate records.
    • Click the small '2' sign will expand all '+', click the small '1' will contract all.
    • To the right there is a new column "Count of Occurances". It tells you, for example, "bob@aol.com" has duplicates and it appears 3 times in the table.

    Find & Remove Duplicates - Dedupe Excel Table List

    Click '+' or '2' to expand and show the duplicates. Click '-' or '1' to contract.

    Find & Remove Duplicates - Dedupe Excel Table List

  4. Extract Unique
    • Make sure the '+' is NOT expanded. Click "Extract Result->Copy Visible Rows to New Sheet", a new sheet will be created, and the visible ones (unique rows with the count of occurrance) will be copied over.

    Find & Remove Duplicates - Dedupe Excel Table List

Find & Remove Duplicates ( dedupe ) - Example #4

I have a list of mailing addresses. Each row is a person's mailing address. Since there are multiple people living at the same address, we have a duplicate problem Need to extract the unique mailing addresses, ( * The uniqueness of a mailing address is determined by 3 columns "Street1", "Street2", "ZipCode" ) and count the number of duplicates for each unique.

    Find & Remove Duplicates - Dedupe Excel Table List
    Practice file - dedupe-mail-list-demo.xls (16k)

    This is pretty much the same as Example #3. Except in step 2, you need to use 'Add Fields' to in order to have 3 determining (key) fields: "Street1", "Street2", "ZipCode". The uniqueness of a mailing address is determined by the three.

    Find & Remove Duplicates - Dedupe Excel Table List

Find & Remove Duplicates ( dedupe ) - Example #5

I have a list of customer transactions, need to find the latest transaction by each customer.

    Find & Remove Duplicates - Dedupe Excel Table List
    Practice file - dedupe-transaction-list-demo.xls (16k)

    This is similiar to Example #3 in that in result you want one row per 'customer'. But the row needs to meet the rule which is the 'date' must be the latest (max value) of all the rows by that customer.

  1. Sort the list by 2 columns - Sort by 'Customer', then by 'Date'

    Find & Remove Duplicates - Dedupe Excel Table List

    Click 'Then by' to add more columns to sort by.

    Find & Remove Duplicates - Dedupe Excel Table List

    Find & Remove Duplicates - Dedupe Excel Table List

    Sorted, so that for each 'customer', its rows are sorted by 'Date', from latest to oldest

    Find & Remove Duplicates - Dedupe Excel Table List

  2. Invoke 'DigDB->Complex Filter->Filter Duplicates...', repeat Example #3

    Find & Remove Duplicates - Dedupe Excel Table List

    Set how to filter

    Find & Remove Duplicates - Dedupe Excel Table List

    Only the first row of each 'Customer' is shown, which is already sorted to be the latest 'Date' (max)

    Find & Remove Duplicates - Dedupe Excel Table List

    Extract the result

    Find & Remove Duplicates - Dedupe Excel Table List

Find & Remove Duplicates ( dedupe ) - Example #6

I need to quickly extract or count unique values in a range, but the range is not a list or table. It's just a random selection of cells. How to do that?

    Find & Remove Duplicates - Dedupe Excel Table List
    Practice file - dedupe-selection-demo.xls (16k)

  1. Select the range(s) first, use Ctrl+select for multiple areas, then invoke 'Unique->Extract Uniques->from Selection'

    Find & Remove Duplicates - Dedupe Excel Table List

    A new sheet will be created where unique values will be extracted

    Find & Remove Duplicates - Dedupe Excel Table List

    You can also use 'Unique->Count Uniques->in Selection' to get a quick count of the unique values in your selected cells.

Need more?


Features | Testimonial | Download | Purchase | FAQ | Contact (free forum)

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.