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

### 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?

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.

2. Wizard Step 1 - List1 is automatically selected.

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

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

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.

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.

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

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.

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

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.

### 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?

• 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 | Excel Resume Examples ( wehtwrjw )