Searching keywords using VBA in excel -not exact match

[Mirandazhou]

Hi everyone, I'm relatively new to VBA and I am really struggling with the following task..

I am creating a huge excel file, and need to find out the names, from one row
[TABLE="width: 500"]
<tbody>[TR]
[TD]James LEE went to mall today[/TD]
[TD]James, Lee[/TD]
[/TR]
[TR]
[TD]Rental payment for Lee James[/TD]
[TD]James, Lee[/TD]
[/TR]
[TR]
[TD]Orders for JAMES LEE[/TD]
[TD]James, Lee[/TD]
[/TR]
[TR]
[TD]Kim Chazen got hired[/TD]
[TD]Kim, Chazen[/TD]
[/TR]
[TR]
[TD]Salary for Kim Chazen[/TD]
[TD]Kim, Chazen[/TD]
[/TR]
</tbody>[/TABLE]

I have keyword table as


[TABLE="width: 500"]
<tbody>[TR]
[TD]Kim, Chazen[/TD]
[/TR]
[TR]
[TD]James, Lee[/TD]
[/TR]
[TR]
[TD]Yang, Wang[/TD]
[/TR]
</tbody>[/TABLE]

The names shows in different format, sometimes James LEE, LEE James, James M Lee, so I cannot use lookup function directly in excel...

I really appreciate for the help! Thanks!

 

[LloydFinancials]

Excel 2010
	A	B	C	D	E
1	James LEE went to mall today	James, Lee			James, Lee
2	Rental payment for Lee James	James, Lee			Kim, Chazen
3	Orders for JAMES LEE	James, Lee
4	Kim Chazen got hired	Kim, Chazen
5	Salary for Kim Chazen	Kim, Chazen
Sheet1

Cell Formulas
Range	Formula
B1	{=INDEX($E$1:$E$2,MATCH(TRUE,ISNUMBER(FIND(UPPER(MID($E$1:$E$2,FIND(",",$E$1:$E$2)+2,LEN($E$1:$E$2))),UPPER(A1))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mirandazhou]

Excel 2010

	A	B	C	D	E
James LEE went to mall today	James, Lee	James, Lee
Rental payment for Lee James	James, Lee	Kim, Chazen
Orders for JAMES LEE	James, Lee
Kim Chazen got hired	Kim, Chazen
Salary for Kim Chazen	Kim, Chazen

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]{=INDEX($E$1:$E$2,MATCH(TRUE,ISNUMBER(FIND(UPPER(MID($E$1:$E$2,FIND(",",$E$1:$E$2)+2,LEN($E$1:$E$2))),UPPER(A1))),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your help. Maybe I did not describe well... the other column (B) is what I want, not shown in excel.......

 

[LloydFinancials]

Still don't understand. Provide a before and after with cell references and cell you want your formula in.

 

[Mirandazhou]

Really sorry for the confusion. It is my first time posting here..

Before:

1	James LEE went to mall today
2	Rental payment for Lee James
3	Orders for JAMES LEE
4	Kim Chazen got hired
5	Salary for Kim Chazen

<tbody>
</tbody>

After:
[TABLE="width: 373"]
<tbody>[TR]
[TD]James LEE went to mall today
[/TD]
[TD]James, Lee
[/TD]
[/TR]
[TR]
[TD]Rental payment for Lee James
[/TD]
[TD]James, Lee
[/TD]
[/TR]
[TR]
[TD]Orders for JAMES LEE
[/TD]
[TD]James, Lee
[/TD]
[/TR]
[TR]
[TD]Kim Chazen got hired
[/TD]
[TD]Kim, Chazen
[/TD]
[/TR]
[TR]
[TD]Salary for Kim Chazen
[/TD]
[TD]Kim, Chazen
[/TD]
[/TR]
</tbody>[/TABLE]

My keywords table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Kim, Chazen
[/TD]
[/TR]
[TR]
[TD]James, Lee
[/TD]
[/TR]
[TR]
[TD]Yang, Wang
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you again for your help!! Really appreciate!

 

[LloydFinancials]

My formula gives you what you want. You just need to update the cell references to match the true locations in your workbook. My $E$1:$E$2 is your keyword table. The formula in B1 is copied down. Be sure to follow the instructions at the bottom of post#2.

 

[Mirandazhou]

Thank you so much LloydFinancials!! Your formula works really well, I thought only VBA can solve the problem before... Thank you!

My formula gives you what you want. You just need to update the cell references to match the true locations in your workbook. My $E$1:$E$2 is your keyword table. The formula in B1 is copied down. Be sure to follow the instructions at the bottom of post#2.

 

[Mirandazhou]

Hi LloydFinancials, sorry to bother you again, I found out an issue when I applied the formula....


My keywords are a large set, say E2-E600, and my dataset is A2-A2000,

When I did the A1500, the keywords was E 350, when I set the match area to be E2-E600, it might appear to be another name, which actually does not include in A1500; If I changed the match area to be E300-E600, the result was different again; of when I change the match area to E340-E360, the result was correct

Do you know why it happens? Thank you a lot!

 

[Mirandazhou]

bump

 

[Mirandazhou]

Anyone any thoughts?