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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Keyword[/TD]
[TD="align: center"]Route[/TD]
[TD="align: center"]Route I want to Return[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]Large Abrasive Tool[/TD]
[TD="align: center"]Tensile[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]22[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]Small Cutting Scissors[/TD]
[TD="align: center"]Abrasive[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]45[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]Difficult Tensile Instrument[/TD]
[TD="align: center"]Cutting[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]87[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]Random Tool[/TD]
[TD="align: center"]Honing[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: center"]Small Abrasive Wheel[/TD]
[TD="align: center"]Drilling[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]22[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Excel 2010
	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
Sheet6

Cell Formulas
Range	Formula
D2	=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A2),$C$2:$C$6),"")
D3	=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A3),$C$2:$C$6),"")
D4	=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A4),$C$2:$C$6),"")
D5	=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A5),$C$2:$C$6),"")
D6	=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A6),$C$2:$C$6),"")

 

[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]

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]

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?