find many based on a certain criteria

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
490
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
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

Active Member
Joined
Jan 26, 2017
Messages
490
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
I suspect that would be difficult with a formula, are you happy for VBA?
 

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
490
hi,

no probs with VB. ...

thank you very much:)
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
How about
Code:
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

Active Member
Joined
Jan 26, 2017
Messages
490
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
Could you upload the Xl file, rather than an HTML file.
 

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
490
DOH….
think I have sorted.... changed the area on sheet 1 .

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
Glad you sorted it & thanks for the feedback
 

Forum statistics

Threads
1,077,825
Messages
5,336,595
Members
399,090
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top