Partial Text Matching HELP PLEASE! SOS! SOS!!

MikeMikeMike · Feb 15, 2011

I am struggling with this and wondering if you could help..

Worksheet 1 Column A & B
Keyword Category
telecom Telecom
tele com #N/A

Worksheet 2 Column A & B
term category
telecom Telecom
telcom Telecom
tele com Telecom

What I want to do is pull the category name from worksheet 2 on to worksheet 1.

=VLOOKUP("*"&A2&"*",Sheet1!B:B,1,0)

Is not working...any ideas? I am not dealing with any duplicates so that is no a worry...

MikeMikeMike · Feb 16, 2011

Ok after having some time with this and trying everything that was suggested by all of you I have to say that Haseebs solution is the one the best fits my problem.

@Haseeb - You are the man...

I am playing with it and will post back but if anyone can help me tweak this

=LOOKUP(2,1/SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

so that I can add more rows to sheet 2 and have those changes reflect on sheet 1 I would be set to go!

Also...wouldnt hurt if someone could help me understand this formula that would be great...

Awesome!

You guys rock!

Aladin Akyurek · Feb 16, 2011

MikeMikeMike said:
Ok after having some time with this and trying everything that was suggested by all of you I have to say that Haseebs solution is the one the best fits my problem.

@Haseeb - You are the man...

I am playing with it and will post back but if anyone can help me tweak this

=LOOKUP(2,1/SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

so that I can add more rows to sheet 2 and have those changes reflect on sheet 1 I would be set to go!

Also...wouldnt hurt if someone could help me understand this formula that would be great...

Awesome!

You guys rock!

You must have missed some posts...

=LOOKUP(2,1/SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

is the same as:

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

which is slightly faster and already suggested to you.

In the end, you might need to apply fuzzy matching.

MikeMikeMike · Feb 16, 2011

Aladin Akyurek said:
You must have missed some posts...

=LOOKUP(2,1/SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

is the same as:

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

which is slightly faster and already suggested to you.

In the end, you might need to apply fuzzy matching.

Hmmm....I will have to play with that one as well. Can you explain a little what is going on there?

I get whats going on in the other one now after spending some time with it so I was able to tweak it.

My problem now and the answer may be what you are suggesting with a fuzzylookup needing to be involved:

The precedence of the phrases...basically if I have jobs in a2 it is overwritten by telecom in a3 which of course defeats the purpose.

Thoughts?

Aladin Akyurek · Feb 16, 2011

MikeMikeMike said:
Hmmm....I will have to play with that one as well. Can you explain a little what is going on there?

SEARCH returns a position indicating number (integer) if it finds the search string within the target string, othwerwise it resturns a #VALUE! error. This formula asks SEARCH to look for a set of search strings in a target string and return the position integer it finds. Exemple: Let E2:E5 house the search strings and F2:F5 the associated categories like in:

FAD,Animal
JAD,Plant
KAD,Animal
LAD,Unknown

Let A1 house: The species of kad faces extinction.

=LOOKUP(9.99999999999999E+307,SEARCH(E2:E5,A1),F2:F5)

will evaluate at some point as

LOOKUP(9.99999999999999E+307,{#VALUE!,#VALUE!,16,#VALUE!},{"Animal","Plant","Animal","Unknown"})

and it will return the category associated with the ssearch string which has been found: 16 belongs to KAD. LOOKUP will always pick out the last integer from the SEARCH results.

...
My problem now and the answer may be what you are suggesting with a fuzzylookup needing to be involved:

The precedence of the phrases...basically if I have jobs in a2 it is overwritten by telecom in a3 which of course defeats the purpose.

Thoughts?

Try to figure out how to use:

http://www.mrexcel.com/forum/showthread.php?t=195635

MikeMikeMike · Feb 16, 2011

Aladin Akyurek said:
SEARCH returns a position indicating number (integer) if it finds the search string within the target string, othwerwise it resturns a #VALUE! error. This formula asks SEARCH to look for a set of search strings in a target string and return the position integer it finds. Exemple: Let E2:E5 house the search strings and F2:F5 the associated categories like in:

FAD,Animal
JAD,Plant
KAD,Animal
LAD,Unknown

Let A1 house: The species of kad faces extinction.

=LOOKUP(9.99999999999999E+307,SEARCH(E2:E5,A1),F2:F5)

will evaluate at some point as

LOOKUP(9.99999999999999E+307,{#VALUE!,#VALUE!,16,#VALUE!},{"Animal","Plant","Animal","Unknown"})

and it will return the category associated with the ssearch string which has been found: 16 belongs to KAD. LOOKUP will always pick out the last integer from the SEARCH results.

Try to figure out how to use:

http://www.mrexcel.com/forum/showthread.php?t=195635

Ok well...I am actually glad you kind of challenged me to go through that. I did get it to work. However to my surprise it does not work either...

Notice that somehow all algos throw washington in Phones, Telecom overwrites everything that precedes it in column A (Jobs and Phones).

Surely this isnt the end of the road for me?

Aladin Akyurek · Feb 16, 2011

1) Posting images is not a good idea for what it shows cannot be copied as data into cells in Excel.

2) The image above does not tell what are the search strings (keywords), their associated categories, and the target strings on which they are applied.

MikeMikeMike · Feb 17, 2011

This forum is frustrating...I did figure it out!

Got it running clean on my local machine...smooth, sweet...really impressive.

Honestly it increased my education and I am truly grateful!

However - It is like the 5TH suggestion that does NOT work for me.

I ended up coming up with what I thought was a separate question (maybe I was wrong) thinking that this thread got out of hand....and instead of being helped I was simply shot down with a quickness without a single suggestion.

Please someone out there help me answer the simple question (that was not the original question) that should not require any further explanation than what I detailed here:

http://www.mrexcel.com/forum/showthread.php?t=529687

dvananthan · Feb 17, 2011

hi

i am fairly new to the forum but then something simple like this will it work?

HTML:

=IF(ISNUMBER(SEARCH("tele",A1)),"Telecom","")

reg

ananth

al_b_cnu · Feb 17, 2011

Hi,

Here's the FuzzyVLookup solution:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Keyword </td><td style="font-weight: bold;;">Category</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">telecom </td><td style=";">TeleCom 1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">tele com </td><td style=";">TeleCom 2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">telecommunications </td><td style=";">TeleCom 1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">tele comm </td><td style=";">TeleCom 2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">better world telecom </td><td style=";">TeleCom 1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">telecommunication </td><td style=";">TeleCom 1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">telecommunications companies </td><td style=";">TeleCom 1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">telecommunications jobs </td><td style=";">TeleCom 1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">telecom jobs </td><td style=";">TeleCom 1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A2,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A3,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A4,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A5,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A6,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A7,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A8,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A9,Sheet2!A:B,2,,,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=FuzzyVLookup(<font color="Blue">A10,Sheet2!A:B,2,,,2</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Term</td><td style="font-weight: bold;;">Category</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Telecom</td><td style=";">TeleCom 1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tele Com</td><td style=";">TeleCom 2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

dvananthan · Feb 17, 2011

hi!

what about this!!

HTML:

=IF(ISNUMBER(SEARCH("jobs",A14:A24)),"jobs",IF(ISNUMBER(SEARCH("voip",A14:A24)),"voip",IF(ISNUMBER(SEARCH("tele",A14:A24)),"telecom","")))

i think you can add six categories on the basia of priority!

regards
ananth

Partial Text Matching HELP PLEASE! SOS! SOS!!

MikeMikeMike

New Member

MikeMikeMike

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

MikeMikeMike

New Member

Aladin Akyurek

MrExcel MVP

MikeMikeMike

New Member

Aladin Akyurek

MrExcel MVP

MikeMikeMike

New Member

dvananthan

Board Regular

al_b_cnu

Well-known Member

dvananthan

Board Regular

Similar threads

Forum statistics

Share this page

Partial Text Matching HELP PLEASE! SOS! SOS!!

New Member

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock