find many based on a certain criteria

[Trevor3007]

good evening,

I hope someone can help me?

I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet 2 that contains a specific word ( ie Adobe)

This would than display the data on sheet 1. A bit like vlookup, but vlookup as you are aware only picks up one line ...
I need this to display anything with the specific word on sheet 1 row by row.

IE
sheet 1 cell a2 adobe

sheet 2 would then find all the data that contains 'adobe' and display the contents of that row(s) into sheet 1 (a3:z1000)

thank you for your time
KR
Trevor3007

 

[Fluff]

Will adobe be found in one particular column?
Also will it be on it's own in the cell, or part of a string?

 

[Trevor3007]

hi Fluff,

thanks for getting back to me.

Will adobe be found in one particular column?
no it could be contained in multi

Also will it be on it's own in the cell, or part of a string?
it would be contained in a string


MTIA

 

[Fluff]

I suspect that would be difficult with a formula, are you happy for VBA?

 

[Trevor3007]

hi,

no probs with VB. ...

thank you very much

 

[Fluff]

How about

Sub Trevor3007()
    Dim Ary As Variant, Nary As Variant
    Dim r As Long, c As Long, cc As Long, nr As Long
    Dim Srch As String
    
    Srch = Sheets("Sheet1").Range("A2").Value
    Ary = Sheets("pcode").Range("A1").CurrentRegion.Value2
    ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
    
    For r = 2 To UBound(Ary)
        For c = 1 To UBound(Ary, 2)
            If InStr(1, Ary(r, c), Srch, vbTextCompare) > 0 Then
                nr = nr + 1
                For cc = 1 To UBound(Ary, 2)
                    Nary(nr, cc) = Ary(r, cc)
                Next cc
                Exit For
            End If
        Next c
    Next r
    Sheets("Sheet1").Range("A3").Resize(nr, UBound(Ary, 2)).Value = Nary
End Sub

 

[Trevor3007]

hello Fluff,


thanks fo your code. works OK..but it does not show all the results? sheet 1 , I enter 'hello' into a2 , sheet 2 has '4' hits with 'hello' but it only returns 3 hits in sheet 1?

I have enclosed the link below , which I hope will explain ?


https://1drv.ms/u/s!AvGGXsEtXRpdhoNsshwmysZdpuWVJw?e=Q23Gau
hoping you can sort ( I had I look at the code, but could not resolve

MTIA

 

[Fluff]

Could you upload the Xl file, rather than an HTML file.

 

[Trevor3007]

DOH….
think I have sorted.... changed the area on sheet 1 .

:}
have a great xmas <{;o}>

 

[Fluff]

Glad you sorted it & thanks for the feedback