Perform Reverse Simple Match

Bill Bisco

Active Member
Joined
Aug 8, 2007
Messages
446
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.

ABCD
1DescriptionKeywordRouteRoute I want to Return
2Large Abrasive ToolTensile8722
3Small Cutting ScissorsAbrasive2245
4Difficult Tensile InstrumentCutting4587
5Random ToolHoning21
6Small Abrasive WheelDrilling1122

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


<tbody>
</tbody>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Excel 2010
ABCD
1DescriptionKeywordRouteRoute I want to Return
2Large Abrasive ToolTensile8722
3Small Cutting ScissorsAbrasive2245
4Difficult Tensile InstrumentCutting4587
5Random ToolHoning21 
6Small Abrasive WheelDrilling1122
Sheet6
Cell Formulas
RangeFormula
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),"")
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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...
Code:
[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]
 
Upvote 0
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!
 
Upvote 0
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...
Code:
[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!
 
Upvote 0
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
 
Upvote 0
It will be because of blank cells i believe. Does your range include blank cells?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top