Searching worksheet for a string and returning list of all matching values

[caststone]

I have a worksheet with many lines of data and I want to be able to find a way to type a string in and be presented with a list of all lines in the worksheet that contain the string, no matter what column they are in. So for example -

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]boy[/TD]
[TD]dog[/TD]
[TD]hat[/TD]
[TD]blue bird[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ball[/TD]
[TD]horse[/TD]
[TD]birdman[/TD]
[TD]car[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]lorry[/TD]
[TD]keys[/TD]
[TD]hand[/TD]
[TD]cup[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bird[/TD]
[TD]dog[/TD]
[TD]boy[/TD]
[TD]ball[/TD]
[/TR]
</tbody>[/TABLE]

I would like to type the word "bird" and be presented with the following list -



[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]boy[/TD]
[TD]dog[/TD]
[TD]hat[/TD]
[TD]blue bird[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ball[/TD]
[TD]horse[/TD]
[TD]birdman[/TD]
[TD]car[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bird[/TD]
[TD]dog[/TD]
[TD]boy[/TD]
[TD]ball[/TD]
[/TR]
</tbody>[/TABLE]


I am not sure where to start. Is this best tackled with VBA? is there something I could do with pivot tables? I want to make it as simple as possible to enter the search term so user does not have to enter wildcards to get the full results.

Any clues would be gratefully received

 

[davesexcel]

Using find, you will get a list of results Ctrl & f

What are you planning on doing with the results.

 

[Fluff]

You could use conditional formatting to highlight the relevant cells.
Would that be ok?

 

[caststone]

Conditional formatting would be a start but the list would be easy to look at if irrelevant lines were not shown

 

[MickG]

Try this for results starting "H1"

[COLOR="Navy"]Sub[/COLOR] MG12Sep16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Mytxt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Mytxt = InputBox("Enter Text", "Text", "Type your Text here")
Columns("H:K").ClearContents
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Join(Application.Transpose(Application.Transpose(Dn.Resize(, 4))), ",")
    [COLOR="Navy"]If[/COLOR] InStr(1, Txt, Mytxt, vbTextCompare) > 0 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Cells(c, "H").Resize(, 4).Value = Dn.Resize(, 4).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[caststone]

I would like to take the resulting list and try and sort or filter on different columns to find items that are really the same but have been entered incorrectly. This would require 'human intervention'.

 

[My Aswer Is This]

So copy the entire row to another sheet if word is found. Would that work?
Copy row to what sheet?

 

[caststone]

Yes that is exactly it. Copied to a results sheet which can then be manipulated without touching the original data.

I will give Mick's suggestion a try in the morning and see what that gives me .

 

[My Aswer Is This]

Try this:
Will copy rows to a sheet named Results

Sub Find_Me()
'Modified  9/12/2018  12:22:01 PM  EDT
Application.ScreenUpdating = False
Dim r As Range
Dim lastrow As Long
lastrow = 1
Dim ans As String
ans = InputBox("Search for what value")
    For Each r In ActiveSheet.UsedRange
        If InStr(r.Value, ans) Then Rows(r.Row).Copy Sheets("Results").Rows(lastrow): lastrow = lastrow + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

What happens if bird is found on a row more than once? e.g. Birdman Blackbird Birds Should this row appear 3 times in Results or just once (i.e. unique rows)?
Why copy entire row, is there a column end point?
Why not copy just the value and next to it mark it's row number then output to Results?
Should the code sort the output ascending A-Z on sheet Results?

MickG's code will output results in same sheet that contains data but doesn't duplicate rows if more than one instance of matching term is found across the row, easily adjusted to output to a Results sheet, so may want the output sorted after it's printed.