Single-column Table Match
I have 2 lists of emails. How to filter/subtract from List1 the emails that are in List2?
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.
Hidden beneath the '+' are the matched rows (duplicates). You can expand '+' or '2' to see what they are. Click the '-' or '1' to contract.
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:
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.
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.
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.
Copyright © 1999-2007 All rights reserved
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