search list for part of text and return all matching results and their ref !

[planetsair]

Hello

I have been searching for an answer but as yet have not managed to find quite the right solution, so I’m hoping someone here may be able to help me, please J
I am wanting to search (using a partial text) through a list of data (held in sheet 2) and have a formula return a list (in sheet 1) of all the ‘words’ from sheet 2 which contain that searched for text, and also their ID

Hopefully the example below will be a bit more explanatory !
In sheet 1 – I am wanting to search for CDE in sheet 2 column A,
I am wanting a list of results to show ALL the occurrences of “CDE” from sheet 2 column A
I am also wanting to have the matching ID be shown in sheet 1 column B

[TABLE="width: 651"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]sheet 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]Search
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]TEXT
[/TD]
[TD]ID
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]CDE
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]ABCDEFGHI
[/TD]
[TD]SK001
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]HYTGREBYD
[/TD]
[TD]SK002
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]results
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]BCGHGICDA
[/TD]
[TD]SK003
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]TEXT
[/TD]
[TD]ID
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]CDEGHYTEGT
[/TD]
[TD]SK004
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]ABCDEFGHI
[/TD]
[TD]SK001
[/TD]
[TD][/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]HYTSBHFR
[/TD]
[TD]SK005
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]CDEGHYTEGT
[/TD]
[TD]SK004
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I don’t know if this is relevant but in reality the text will be about 20-30 characters in length and the search criteria will also be over 15 characters (rather than the ‘CDE’ shown here)

Thanks for your help in advance
Sarah

 

[mumps]

Try this macro:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Dim searchVal As String
    searchVal = InputBox("Please enter search criteria.")
    For Each rng In Sheets("Sheet2").Range("A2:A" & bottomA)
        If rng Like "*" & searchVal & "*" Then
            rng.Resize(, 2).Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[planetsair]

Hi Mumps

Thanks very much for this suggestion, but I’m not really wanting to use macros. Is there any other way I can do this ?

Cheers
Sarah

 

[planetsair]

Update ….

Using this
=INDEX(sheet2!A:A,MATCH("*"&$A$2&"*",sheet2!A:A,0),1)

I can get a single matching cell contents, so does anyone know how I can expand this formula so I can get all the matching cell’s contents and their respective IDs ?

 

[Aladin Akyurek]

[TABLE="width: 328"]
<COLGROUP><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7139" width=201><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6115" width=172><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl65, width: 201, bgcolor: white"]Search[/TD]
[TD="class: xl66, width: 172, bgcolor: white"] [/TD]
[TD="class: xl66, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 201, bgcolor: white"]CDE[/TD]
[TD="class: xl66, width: 172, bgcolor: white"] [/TD]
[TD="class: xl66, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 201, bgcolor: white"] [/TD]
[TD="class: xl66, width: 172, bgcolor: white"] [/TD]
[TD="class: xl66, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 201, bgcolor: white"]results[/TD]
[TD="class: xl66, width: 172, bgcolor: white"] [/TD]
[TD="class: xl66, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]IDX[/TD]
[TD="class: xl66, width: 172, bgcolor: white"]TEXT[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]ID[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, width: 172, bgcolor: white"]ABCDEFGHI[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]SK001[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl66, width: 172, bgcolor: white"]CDEGHYTEGT[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]SK004[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


A6, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH($A$2,Sheet2!$A$2:$A$6)),
  ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1),ROWS($A$6:A6)),"")

B6, just enter, copy across, and down:

=IF($A6="","",INDEX(Sheet2!A$2:A$6,$A6))

 

[planetsair]

Hi Aladin
That is fantastic – just what I was wanting thank you
Just to finish it off though – how would I get the total count of the all matches obtained from the search?
Thanks
sarah

 

[Aladin Akyurek]

Hi Aladin
That is fantastic – just what I was wanting thank you

Great. You are welcome.

Just to finish it off though – how would I get the total count of the all matches obtained from the search?
Thanks
sarah

The following will do so:

=SUMPRODUCT(ISNUMBER(SEARCH($A$2,Sheet2!$A$2:$A$6))+0)

 

[planetsair]

Hi Aladin

Sorry for the slow reply – I have been away from my PC all weekend

Thanks for the new formula – that’s just what I was looking for
Thanks heaps
Sarah

 

[Aladin Akyurek]

Hi Aladin

Sorry for the slow reply – I have been away from my PC all weekend

Thanks for the new formula – that’s just what I was looking for
Thanks heaps
Sarah

No problem. You are welcome.