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!
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
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!
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
