Search for keywords in another sheet

[madhav_kulkarni]

Sheet2 contains more than 200000 rows (with unique strings in Column A and B). I would like to search 1 to 3 keywords from Sheet1, Keywords in B1,B2,B3 and get all matching cells from Column A from Sheet2 to Sheet1, starting with Cells A5. For Column B (B5 onwards), I am using vlookup to get matching column B for each of Column A values.

Following formula does the job but is too slow as the processor keeps counting and the process starts immediately after 1st keyword is entered.. entering 2nd and/or 3rd keyword takes long time... and then retrieval takes even longer time

=IF($B$1<>"",IFERROR(INDEX(Sheet2!$A$2:$B$260169, SMALL(IF(ISERROR(SEARCH($B$1, Sheet2!$A$2:$A$260169&Sheet2!$B$2:$B$260169)*SEARCH($B$2, Sheet2!$A$2:$A$260169&Sheet2!$B$2:$B$260169)*SEARCH($B$3, Sheet2!$A$2:$A$260169&Sheet2!$B$2:$B$260169)),"", ROW(A2:A260169)-MIN(ROW(A2:A260169))+1), ROW(A1)), COLUMN(A1)),""),"")

A macro to quickly look for 1-3 keywords from Sheet1 in Column A of Sheet2 and then getting matching values from Column A and B to Sheet1 would be of great help.

 

[Aladin Akyurek]

Why do you concatenate the A range and the B range of Sheet2 while a key word you are looking for must occur in the A range?

 

[mikerickson]

My first thought is that Advanced Filter has a Copy To Other Location feature, but it might choke on 200,000 rows.

 

[madhav_kulkarni]

@Aladin.. I do not concatenate.. the formula probably looks into any of A or B column and return column A..

 

[Aladin Akyurek]

@Aladin.. I do not concatenate.. the formula probably looks into any of A or B column and return column A..

You do. Understandable if a keyword you are looking for might occur in either the A range or the B range. If this is indeed the case, let's try the following in order to see whether we can get a bearable performance...

In C2 of Sheet2 enter and copy down:

=$A2&"|"&$B2

In B4 of Sheet1 enter:

=SUMPRODUCT(ISNUMBER(SEARCH(B1:B3,Sheet2!C2:C260169))+0)

In B5 control+shift+enter, not just enter:

=IF(ROWS($B$5:B5)>$B$4,"",INDEX(Sheet2!$A$2:$B$260169,SMALL(IF(ISNUMBER(SEARCH($B$1:$B$3,Sheet2!$C$2:$C$260169)),ROW($A$2:$A$260169)-ROW($A$2)+1),ROWS($B$5:B5))))

 

[Peter_SSs]

A macro to quickly look for 1-3 keywords from Sheet1 in Column A of Sheet2 and then getting matching values from Column A and B to Sheet1 would be of great help.

Does this one do what you want?
Test in a copy of your workbook.

Sub Get_Values()
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    With .Range("A4", .Range("B" & .Rows.Count).End(xlUp))
      .AutoFilter Field:=1, Criteria1:=Split(Join(Application.Transpose( _
                  Sheets("Sheet1").Range("A1").CurrentRegion.Resize(, 1).Value), "|"), "|"), Operator:=xlFilterValues
      .Offset(1).Copy Destination:=Sheets("Sheet1").Range("A5")
      .AutoFilter
    End With
  End With
  Application.ScreenUpdating = True
End Sub

Edit: I did that in a bit of a hurry & I think I have mis-interpreted the requirement. I will look at it again later when I have some more time.

 

[Rkeev]

Just a thought.....

Not tested:

If you have the ability to list your 3 keywords on the same sheet you are searching; such as use G1:G3, of sheet 2
In a helper column enter:

   =If(OR(ISNUMBER(LOOKUP(2^15,SEARCH($G$1:$G$3,A5))),ISNUMBER(LOOKUP(2^15,SEARCH($G$1:$G$3,B5)))),"True","False")

then filter on true or use an advance filter using the formula to move to the desired sheet (which can be recorded if you desire), but again the speed may depend on the number of rows filtered by advance filtering.

Rkeev

 

[Aladin Akyurek]

Just a thought.....

Not tested:

If you have the ability to list your 3 keywords on the same sheet you are searching; such as use G1:G3, of sheet 2
In a helper column enter:

   =If(OR(ISNUMBER(LOOKUP(2^15,SEARCH($G$1:$G$3,A5))),ISNUMBER(LOOKUP(2^15,SEARCH($G$1:$G$3,B5)))),"True","False")

then filter on true or use an advance filter using the formula to move to the desired sheet (which can be recorded if you desire), but again the speed may depend on the number of rows filtered by advance filtering.

Rkeev

If we would want this route, we can be concise:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($G$1:$G$3,A5&"|"&B5)))

 

[madhav_kulkarni]

Thanks Peter. The code did not work. I am trying to attaché the file but did not get the option. I must be missing something.
So pasting the stuff from 2 sheets here:

Sheet 1:

COLUMN A COLUMN B
[TABLE="width: 506"]
<tbody>[TR]
[TD]STRING 1[/TD]
[TD]RAIN[/TD]
[/TR]
[TR]
[TD]STRING 2[/TD]
[TD]BUILDING[/TD]
[/TR]
[TR]
[TD]STRING 3[/TD]
[TD]SLOPE[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

(Expected outcome below):
[TABLE="width: 506"]
<tbody>[TR]
[TD]TEST A[/TD]
[TD]TEST B[/TD]
[/TR]
[TR]
[TD]RAIN HARVESTING, BUILDING, DESIGN, SLOPE[/TD]
[TD]BUILDING DESIGN[/TD]
[/TR]
[TR]
[TD]HEAVY RAINFALL, SLOPE, BUILDING[/TD]
[TD]BUILDING DESIGN 2[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


Sheet 2:
COLUMN A COLUMN B
[TABLE="width: 487"]
<tbody>[TR]
[TD]TEST A[/TD]
[TD]TEST B[/TD]
[/TR]
[TR]
[TD]FISHING ROD, DRIFTING, METAL, STEEL[/TD]
[TD]FISHING PROBLEM[/TD]
[/TR]
[TR]
[TD]RAIN HARVESTING, BUILDING, DESIGN, SLOPE[/TD]
[TD]BUILDING DESIGN[/TD]
[/TR]
[TR]
[TD]CROP HARVESTING, AGRICULTURE, PESTICIDE, DRIFT CONTROL[/TD]
[TD]AGRICULTURE[/TD]
[/TR]
[TR]
[TD]RAIN FALL, HEAVY, RIVER[/TD]
[TD]AMAZON[/TD]
[/TR]
[TR]
[TD]HEAVY RAINFALL, SLOPE, BUILDING[/TD]
[TD]BUILDING DESIGN 2[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Peter_SSs]

Thanks Peter. The code did not work.

Yes, I realised that I had mis-interpreted the requirement just after posting.

I am trying to attaché the file but did not get the option. I must be missing something.

What you are missing is that we do not allow attachments in this forum. Your example is an excellent reason for why. Your little sample in post #9 is much easier for us to deal with than looking at something with 200,000 rows and trying to work out what you are doing.

If you do want to post sample data in a slightly better way in the future, then follow the 'Look Here' link in my signature block below.


I am not quite sure if you always have 3 values in those cells at the top of column B in Sheet1. For the code below I have assumed that is the case so at least test with that being so.
If we have to allow for less than 3 please post back and advise if B4 is always empty or not.

Give this a try in a copy of your workbook.
With that same Sheet2 sample data copied down 200,000 rows the code below took just over 1 second to return the approximately 80,000 result rows on my pretty old machine.

Sub GetValues()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, k As Long
  
  a = Sheets("Sheet2").Range("A1").CurrentRegion.Value
  ReDim c(1 To UBound(a), 1 To 2)
  b = Sheets("Sheet1").Range("B1:B3").Value
  For i = 1 To UBound(a)
    If InStr(1, a(i, 1), b(1, 1), 1) > 0 Then
      If InStr(1, a(i, 1), b(2, 1), 1) > 0 Then
        If InStr(1, a(i, 1), b(3, 1), 1) > 0 Then
          k = k + 1
          c(k, 1) = a(i, 1): c(k, 2) = a(i, 2)
        End If
      End If
    End If
  Next i
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    .UsedRange.Resize(, 2).Offset(4).Clear
    With .Range("A5:B5").Resize(k)
      .Value = c
      .Columns.AutoFit
    End With
  End With
  Application.ScreenUpdating = True
End Sub