easy search

[job101]

hi
I have a workbook containing the names of 6,500 horses, their sire and their racing form, which is added to daily. I have the horses saved on 13 tabs and would like t create a search box that will give me up to 10 entries. Should I combine all tabs and search or is there a formula wherby I can keep the thirteen tabs?
PLEASE HELP!!!

 

[AlanSidman]

In Power Query, join all tables. Build a parameter as shown in the attached.

 

[MikeVol]

In Power Query

Or using vba

Option Explicit

Sub SearchAndOutput()
    Dim ws          As Worksheet
    Dim i           As Long

    ' Requesting the desired value from the user
    Dim searchValue As String
    searchValue = InputBox("Enter a value to search for:", "Search")
    If searchValue = "" Then Exit Sub

    ' Create an array to store the results
    Dim tempList    As Collection
    Set tempList = New Collection

    ' We go through all the pages of the book
    For Each ws In ThisWorkbook.Worksheets

        If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then

            ' Find the last row in column A
            Dim lastRow As Long
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

            If lastRow >= 1 Then

                ' Loading data from columns A:C into an array
                Dim dataArr As Variant
                dataArr = ws.Range("A1:C" & lastRow).Value

                ' We go through the array
                For i = 1 To UBound(dataArr, 1)

                    If CStr(dataArr(i, 1)) = searchValue Then

                        ' Add the entire string to the collection
                        tempList.Add Array(dataArr(i, 1), dataArr(i, 2), dataArr(i, 3))
                    End If

                Next i

            End If

        End If

    Next ws

    ' If there are any found values, write them to Sheet2 starting with B3
    If tempList.Count > 0 Then

        Dim outputArr() As Variant
        ReDim outputArr(1 To tempList.Count, 1 To 3)

        For i = 1 To tempList.Count
            outputArr(i, 1) = tempList(i)(0)
            outputArr(i, 2) = tempList(i)(1)
            outputArr(i, 3) = tempList(i)(2)
        Next i

        ' Clearing previous results
        ThisWorkbook.Worksheets("Sheet2").Range("B3:D" & Rows.Count).ClearContents

        ' Writing an array to a sheet
        ThisWorkbook.Worksheets("Sheet2").Range("B3").Resize(UBound(outputArr, 1), 3).Value = outputArr
    Else
        MsgBox "The horse name you were looking for was not found!"
    End If

End Sub