I have a column, 'Name' that contains variations of multiple company names - eg. 'Apple', 'Apple Inc.', 'APPL' etc. I am trying to run this spreadsheet through a workflow where it returns all the columns already in the worksheet + a new column with the normalised version of the names. Basically, you pick one between 'Apple', Apple Inc.' and 'APPL' and apply it all companies that you know are Apple.
I'm thinking of running a fuzzy search and then using its output as a lookup table to create Find/Replace workflow.
Any thoughts on how this can be done will be much appreciated.
Thank you in advance :)
Sample set of columns I am working
ID | Name | Address1 | Address2 | City | State | Province | Country | Zip | SectorID | ParentID | DateCreated | CreatedBy | DateModified | ModifiedBy |
1 | Apple | 300 Madison Ave | NULL | New York | New York | NULL | United States | 10017 | 13 | NULL | 56:00.5 | 1 | NULL | NULL |
2 | Microsoft Corporation | 1 Microsoft Way | NULL | Redmond | Washington | NULL | United States | 98052-6399 | 15 | NULL | 57:01.4 | 1 | 58:45.1 | 1 |
3 | APPL | One Infinite Loop | NULL | Cupertino | California | NULL | United States | 95014-2083 | 15 | NULL | 04:40.2 | 1 | NULL | NULL |
4 | MSFT | 1 Microsoft Way | NULL | Redmond | Washington | NULL | United States | 98052-6399 | 15 | NULL | 57:01.4 | 1 | 58:45.1 | 1 |
5 | VMware, Inc. | 3401 Hillview Ave | NULL | Palo Alto | California | NULL | United States | 94304 | 15 | 4 | 02:01.4 | 1 | NULL | NULL |
6 | Hewlett-Packard Company | 3000 Hanover Street | NULL | Palo Alto | California | NULL | United States | NULL | 15 | NULL | 02:54.4 | 1 | 31:09.4 | 5 |
7 | Apple, Inc. | One Infinite Loop | NULL | Cupertino | California | NULL | United States | 95014-2083 | 15 | NULL | 04:40.2 | 1 | NULL | NULL |
8 | HP | 700 East Middlefield Road | NULL | Mountain View | California | NULL | United States | 94043-4033 | 15 | NULL | 05:52.7 | 1 | NULL | NULL |
9 | HP Inc | 700 East Middlefield Road | NULL | Mountain View | California | NULL | United States | 94043-4033 | 15 | NULL | 05:52.7 | 1 | NULL | NULL |
Solved! Go to Solution.
One way would be to try fuzzy matching on the company name, then take the ones that didn't match and try fuzzy matching on address. The attached workflow missed matching HP with the full Hewlett-Packard name because the names are so dissimilar and the addresses are not the same, but it caught all Apple and Microsoft records.
This is great, Philip. Thank you for the help!
Thank you PaulRB! Much appreciated
User | Count |
---|---|
35 | |
28 | |
8 | |
7 | |
7 |