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

Match Tables (lists) by Columns in Excel

Match one table (list) against another by comparing values in the corresponding column pair(s), find or exclude the matched.
  1. Single-column Table Match - I have 2 lists of emails. Need to filter/subtract from List1 the emails that are in List2.
  2. Multiple-column Table Match - 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.
  3. Fuzzy Match - 'Joe Smith' matches 'Smith, Joe', or '121 Grant Rd' matches '121 Grant Road'

Single-column Table Match

I have 2 lists of emails. How to filter/subtract from List1 the emails that are in List2?
    Match Tables (lists) by Columns in Excel
    Practice file - match-email-list-demo.xls (16k)

  1. Go to List1. Click only 1 cell, then invoke "DigDB->Complex Filter->Table Match...", a wizard will start.

    Match Tables (lists) by Columns in Excel

  2. Wizard Step 1 - List1 is automatically selected.

    Match Tables (lists) by Columns in Excel

  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 auto-select List2

    Match Tables (lists) by Columns in Excel

    Match Tables (lists) by Columns in Excel

    Match Tables (lists) by Columns in Excel

  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'.

    Match Tables (lists) by Columns in Excel

  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.

    Match Tables (lists) by Columns in Excel

  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.

    Match Tables (lists) by Columns in Excel

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

    Match Tables (lists) by Columns in Excel

  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.

    Match Tables (lists) by Columns in Excel

Multiple Column Table Match

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")

    Match Tables (lists) by Columns in Excel
    Practice file - match mail-list-demo.xls - (16k)

    This is pretty much the same as the above example. 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.

    Match Tables (lists) by Columns in Excel

Fuzzy Match

Fuzzy match is a very difficult problem. A lot of users ask us if DigDB can fuzzy match for example, 'Joe Smith' to 'Smith, Joe', or '121 Grant Rd' to '121 Grant Road'. The short answer is no. But the long answer is the following:
  1. DigDB's Table Match allows some degree of fuzziness. For example, the matching ignores text cases. Therefore, 'ABC' matches 'abc'. Also, DigDB matches the values, so '1/20/2004' matches 'Jan 20, 2004', in other words, formatting are ignored in a DigDB table match.

  2. In case of abbreviations like: '121 Grant Rd' vs '121 Grant Road'

    You can use DigDB's other data manipulation functions to process the columns before doing the table match. For example, in an mail address situation, there is a finite set of cases like 'Rd' for 'Road', 'St' for 'Street', 'Blvd' for 'Boulevard'. You do a complex filter with criteria being Street1 like *Road, this will get you all the rows with Stree1 column resembling pattern like '... Road'. Select the matched range in the column, invoke Excel's Replace function to replace 'Road' by 'Rd'. Repeat this for 'St' and 'Blvd'. This will clean up and standardize the data in 'Street1' column. After this, you can do the table match and get the result you want.

  3. In case of first name and last name like: 'Joe Smith' vs 'Smith, Joe'

    Use DigDB->Complex Filter to filter for 'Name like *,*' this will get you all the rows with 'Name' value resembling the pattern 'Smith, Joe'. Extract these rows to a new sheet. Use DigDB->Column->Split->by Delimiter function to split the 'Name' column to two columns before and after the ','. Use DigDB->Column->Shift to shift the second column to before the first. Then use DigDB->Column->Combine to combine the 2 columns. Now, you will get values like ' Smith Joe,'. This is close. Continue with the data massaging. Do a DigDB->Column->Trim, this will trim the preceding or trailing spaces in the column. Then Select the column, do an Excel Replace of ',' by blank, to get rid of the ','. Now, you have transformed all values like 'Joe, Smith' to 'Smith Joe'. Append this transformed table back to the original table. You can go ahead and do the table match now.

  4. Fuzzy match with rounding

    Sometimes in one column, you have numbers like '2.345', '2.531', in the other column, you have numbers like '2.3', '2.5'. Suppose you care only the integer and first digit, and you consider '2.345' and '2.3' a match. For table match in such situations, you can use DigDB's rounding tools to round off the first column. Then do the table match.

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.