Perform Reverse Simple Match

[Bill Bisco]

Hi all,

Every Keyword has a route associated with it. I want to find out which description contains the keyword and then post the associated route for that keyword .

For example, Abrasive is associated with Route 22. I want to match the Description "Large Abrasive Tool" to the keyword Abrasive. and now in the same row as Large Abrasive Tool I want to post the associated route of the keyword contained within "Large Abrasive Tool" (which is Abrasive) which should be Route 22.

	A	B	C	D
1	Description	Keyword	Route	Route I want to Return
2	Large Abrasive Tool	Tensile	87	22
3	Small Cutting Scissors	Abrasive	22	45
4	Difficult Tensile Instrument	Cutting	45	87
5	Random Tool	Honing	21
6	Small Abrasive Wheel	Drilling	11	22

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

<tbody>
</tbody>

 

[VBA Geek]

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Description</td><td style="font-weight: bold;text-align: center;;">Keyword</td><td style="font-weight: bold;text-align: center;;">Route</td><td style="font-weight: bold;text-align: center;;">Route I want to Return</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">Large Abrasive Tool</td><td style="text-align: center;;">Tensile</td><td style="text-align: center;;">87</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">Small Cutting Scissors</td><td style="text-align: center;;">Abrasive</td><td style="text-align: center;;">22</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">Difficult Tensile Instrument</td><td style="text-align: center;;">Cutting</td><td style="text-align: center;;">45</td><td style="text-align: right;;">87</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">Random Tool</td><td style="text-align: center;;">Honing</td><td style="text-align: center;;">21</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">Small Abrasive Wheel</td><td style="text-align: center;;">Drilling</td><td style="text-align: center;;">11</td><td style="text-align: right;;">22</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A2</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A3</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A4</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A5</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A6</font>),$C$2:$C$6</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Bill Bisco]

Wow, you're good! Thank you!

 

[Bill Bisco]

Hi I noticed that these formulas start failing if I put in a large number of rows. For example if I do B2:B1000 and C2:C1000 suddenly my answers go to a value of 0 can you tell me why and how to avoid this?

 

[VBA Geek]

ADVERTISEMENT

Did you update the formula accordingly?

=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A2),$C$2:$C$6),"")

the 6 needs to become the new last row

 

[Rick Rothstein]

If you don't need the live update nature of formulas, then you can eliminate all those formulas and use this macro to return the results you want to Column D...

[table="width: 500"]
[tr]
	[td]Sub Routes()
  Dim R As Long, LastRow As Long, Keywords As Variant, FilteredRng As Range
  LastRow = Range("A1").CurrentRegion.Rows.Count
  Keywords = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
  Application.ScreenUpdating = False
  For R = 2 To UBound(Keywords)
    If Application.CountIf(Range("A:A"), "*" & Keywords(R, 1) & "*") Then
      Range("A1").AutoFilter 1, "=*" & Keywords(R, 1) & "*", VisibleDropDown:=False
      Set FilteredRng = Intersect(Columns("D"), Range("2:" & LastRow).SpecialCells(xlVisible))
      If FilteredRng.Rows.Count Then
        FilteredRng.Value = Keywords(R, 2)
      End If
    End If
  Next
  ActiveSheet.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[Bill Bisco]

ADVERTISEMENT

Did you update the formula accordingly?

=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A2),$C$2:$C$6),"")

the 6 needs to become the new last row

Hi VBA Geek. Yes, I modified both of those. Try your own code again, and replace the 6 with a 1000. Suddenly Excel craps out and shows a 0!

 

[Bill Bisco]

If you don't need the live update nature of formulas, then you can eliminate all those formulas and use this macro to return the results you want to Column D...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Routes()
  Dim R As Long, LastRow As Long, Keywords As Variant, FilteredRng As Range
  LastRow = Range("A1").CurrentRegion.Rows.Count
  Keywords = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
  Application.ScreenUpdating = False
  For R = 2 To UBound(Keywords)
    If Application.CountIf(Range("A:A"), "*" & Keywords(R, 1) & "*") Then
      Range("A1").AutoFilter 1, "=*" & Keywords(R, 1) & "*", VisibleDropDown:=False
      Set FilteredRng = Intersect(Columns("D"), Range("2:" & LastRow).SpecialCells(xlVisible))
      If FilteredRng.Rows.Count Then
        FilteredRng.Value = Keywords(R, 2)
      End If
    End If
  Next
  ActiveSheet.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Rick, there's no doubt that you're an Excel Genius. This might work, but I'd much prefer for it to be formula based. I've been banging my head against a wall because this should be intuitively easy yet it is not for me!

 

[VBA Geek]

Hi VBA Geek. Yes, I modified both of those. Try your own code again, and replace the 6 with a 1000. Suddenly Excel craps out and shows a 0!

'It should not do that. Can you post your whole 1000 rows or better yet upload a sample to dropbox

 

[steve the fish]

It will be because of blank cells i believe. Does your range include blank cells?