DigDB 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
- Match two tables (lists), compare by columns, find or exclude the matched
- Example #1 - I have 2 lists of emails. Need to filter/subtract from List1 the emails that are in List2.
- Example #2 - I have 2 lists of mailing addresses. Need to filter/subtract from List1 the mailing addresses that are in List2.
* Note that mailing address is more complicated than email address, because we need to compare at least three columns, 'Street1', 'Street2', 'ZipCode',
to decide if two addresses are the same.
- Filter duplicates within one table (list), by key columns or more complex rules
- Example #3 - I have a list of emails. Need to extract the uniques, and count the number of duplicates for each unique email.
- Example #4 - I have a list of mailing addresses. Need to extract the uniques, ( * The uniqueness of a mailing address
is determined by 3 columns "Street1", "Street2", "ZipCode" ) and count the number of duplicates for each unique address.
- Example #5 - I have a list of customer transactions, need to find the latest transaction by each customer.
- Extract unique values from a random range or areas
- Example #6 - I need to quickly extract a list of the unique values from one or more selected ranges.
Find & Remove Duplicates ( dedupe ) - Example #1
I have 2 lists of emails. How to filter/subtract from List1 the emails that are in List2?
Practice file - dedupe-email-list-demo.xls (16k)
- 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...")
- Wizard Step 1 - List1 is automatically selected.
- 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
- 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'.
- 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.
- 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.
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.
- 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 ) - 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")
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 ) - Example #3
I have a list of emails. How to extract the uniques, and count the number of duplicates for each unique email?
- 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...")
Practice file - dedupe-email-list-demo.xls (16k)
The list will be automatically selected, and a pop-up window will show.
- 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".
- 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.
Click '+' or '2' to expand and show the duplicates. Click '-' or '1' to contract.
- 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 ) - 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.
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 ) - Example #5
I have a list of customer transactions, need to find the latest transaction by each customer.
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.
- Sort the list by 2 columns - Sort by 'Customer', then by 'Date'
- Click a cell in the list, invoke 'DigDB->Sort->Multi-level...', the list will be auto-selected
- Use 'Then by' button to add sort-by columns
- Sort by 'Customer', then by 'Date', 'Descending'
Click 'Then by' to add more columns to sort by.
Sorted, so that for each 'customer', its rows are sorted by 'Date', from latest to oldest
- Invoke 'DigDB->Complex Filter->Filter Duplicates...', repeat Example #3
Set how to filter
Only the first row of each 'Customer' is shown, which is already sorted to be the latest 'Date' (max)
Extract the result
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?
Practice file - dedupe-selection-demo.xls (16k)
- Select the range(s) first, use Ctrl+select for multiple areas, then invoke 'Unique->Extract Uniques->from Selection'
A new sheet will be created where unique values will be extracted
You can also use 'Unique->Count Uniques->in Selection' to get a quick count of the unique values in your selected cells.
Need more?
- Browse the complete list of DigDB's powerful features listed on the front page.
- If you see errors or things not working, check out our error reference page.
Features |
Testimonial |
Download |
Purchase |
FAQ |
Contact
Copyright © 1999-2007 All rights reserved
DigDB 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 and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.