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

		sheet 1					sheet 2
		a	b				a	b
1		Search			1		TEXT	ID
2		CDE			2		ABCDEFGHI	SK001
3					3		HYTGREBYD	SK002
4		results			4		BCGHGICDA	SK003
5		TEXT	ID		5		CDEGHYTEGT	SK004
6		ABCDEFGHI	SK001		6		HYTSBHFR	SK005
7		CDEGHYTEGT	SK004

<tbody>
</tbody>

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]

Search
CDE
results
IDX	TEXT	ID
1	ABCDEFGHI	SK001
4	CDEGHYTEGT	SK004

<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>
</TBODY>

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.