Data Matching Function / tool - for not exact matches...

Chelsea_indoors

New Member
Joined
Oct 12, 2006
Messages
4
Hi

I came across this site various times already and it often provided me with very valuable information (specifically giving tips & tricks on how to do things...).

Currently, I am trying to find a solution for the following problem:

We have various legacy systems in use based on which operational data is extracted and a master customer database is compiled. The db is rather large (~200’000 accounts with individual account names) and I would like to automate a text matching (in fact grouping) of customers, preferably with an Excel function, meaning I would like to be able to do the following:

- Let Excel find a match (or close match) of the ‘Account Name’, in order to group it to a ‘Customer Group’. As the quality of data within ‘Account Name’ is rather poor, filtering (by precise value or by custom filter) does not really help and I would like to prevent having to do everything manually…
[Example: please refer to the extract of data below: index number 7: a full match with ‘Account Name’ not possible, because of misspellings in “rep.” & “Mulbaech”; Index number 8: the match with “Nestle” not possible, typo]

Is there another function within excel available I am not aware of (‘match’ does not seem to help…) or otherwise a tool on the market, facilitating such aligning/matching processes? I am thinking of a ‘google approach’ of suggesting close matches.

Many Thanks! :p

Microsoft Excel - Book2 ___Running: 11.0 : OS = Windows XP

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

A2 =


A B C D E F G
2 Index Account_ID Account_Name Zip_Code_City Sales_Area Opening_Date Customer_Group
3 1 150043217 Pepsi CH North 3002 Basel 01 2004_08 PEPSI
4 2 150048722 Pepsi CH South 2400 Locarno 06 2006_04 PEPSI
5 3 150054227 Nestlé Zurich, 8001 8001 Zurich 02 2002_05 NESTLE
6 4 150059732 Adidas, re. Mulbach 3001 Bern 03 2004_09 ADIDAS
7 5 150065237 Adidas, re. Mulbach 8200 Chur 07 2006_05 ADIDAS
8 6 150070742 Peps$ CH East 5004 St. Gallen 02 2002_06 PEPSI
9 7 150076247 Adidas, rep. Mulbaech 1200 Geneva 08 2004_10 ????
10 8 150081752 Nestél Zurich, 8048 8048 Zurich 02 2006_06 ????
11 9 150087257 UBS 8002 Zurich 02 2002_07 UNION BANK OF S.
12 10 150092762 UB-S 1000 Lausanne 08 2004_11 ????
13 11 150092763 Nestor AG 5432 Würenlos 04 2004_02 NESTOR
14 12 150092764 Nestor AG 5432 Würenlos 04 2004_02 NESTOR
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top