Approximate matching of strings

[santhan]

Hi,


I have two lists to map..


For example,


List 1 List 2

Bulk/Water All Other Isotonic Beverages

ENHANCED BEVERAGES Per tea

ISOTONIC BEVERAGES Bus tea

KIDS DRINKS Bulk water

MULTI_SERVE_JUICE Multi serve juice

TEA


Desired output :

Bulk/water should be mapped to Bulk water,

Tea to both per tea and bus tea

Multi_serve_juice to Multi serve Juice

Isotonic beverages to All Other Isotonic Beverages

etc


Have shared the file at the link below:


http://www.2shared.com/file/7PIytYUK/approximate_matching_of_lists.html


I could map few things directly using functions/formulae but struck with special characters like '_' , '/', etc - couldn't map few others.


Any help would be greatly appreciated.


Thanks in advance!


~ Santhan

 

[SirJB7]

Hi, santhan!

I was wondering if you accept using some additional columns. If yes, here I uploaded your Excel book modified. Hope it works for you.

Column C: copies the last column (list 1 clean)

Column D:

D1 : string with the characters you want to clean

D2:DXX : copies original list

Columns E thru E+len(D1):

Cell (X)1 : character to be cleaned in that column

Celss (X)2X)XX : original string with cleaned accumulations

When you're done with this, you can process column C as usual.

Regards.

Link: http://www.2shared.com/file/GJuDF5Vx/approximate_matching_of_lists_.html

 

[santhan]

Hello,


Thanks a lot. This should help...but the main problem arises when i try to map column C ( list 1 clean) to List 2 ( column B)...for example, if i choose to approximate match 'tea-l5603' in list 1 clean, i get an error output. It is mapping neither to 'Bus tea' nor 'per tea' in list 2. I am not sure if this is possible to achieve !


Thanks again!


~ Santhan

 

[SirJB7]

Hi, santhan!

Can you explain what exactly means "when I try to map Column C"? How do you do that?, I don't see the method or formulaes in your originally uploaded file, if I'm not wrong.

Regards!

 

[NARAYANK991]

Hi ,


The only way is to do the following :


1. Strip each list of all delimiters ( replace with spaces or any other method ) , and extract the words into different columns ; there are sites which give the required formulae to do this.


2. Compare the two sets of keywords to see if there is even one match between the two sets.


Narayan