Scroll Through Multiple Search Results

[dhunton]

I have a bit of an odd workbook (I know, that's not unusual here ). There are two sheets: the second is the data page, the first is a search of sorts for the data on the second.

The search allows the end users (and I'm trying to allow for those who barely know Excel) to type in a phone number OR part of a name. (Defaults to using the phone number if both are entered, removes spaces/dashes/dots from phone before searching.) It will then search in the appropriate field on the second page and display all columns of the row where that is phone or name is found. The final data sheet will have approximately 50-60,000 rows, as an FYI.

It all works as expected. However, when searching for names, it only finds the first person with John in their name (John Adams would be before Steve Johnson, so Steve would never be found). I know this is the expected result. However, I am trying to find a way around this. I have thought about different ways that multiple results in a search can be displayed, and I would be ok with any of these (although I don't know that any are possible here):

1. Displays the first result, then on a click (arrow, mouse, other key) moves to the next result
2. Displays all results in a table on the search page

I am currently using a Match/Index combination to do the lookup.

I've created a sample workbook which you can download here: <a href="PostingFile.xlsx"></a>

Any ideas that you might have would be appreciated. I'm not looking for exact formulas, etc, more looking for directions to research, as I'm at a loss right now.

Thanks for any help you can give.

 

[GWteB]

The Advanced Filter of Excel is what I'm thinking of. Your workbook might need some modifications (I've not downloaded it ...). I nice tutorial is this one (YouTube)

 

[juddaaaa]

You could do it in VBA

Sample HERE

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cc As Range, fnd As Range
    Dim num As String, first_address As String
    Dim last_row As Long
    
    last_row = Cells(Rows.Count, "A").End(xlUp).Row
    
    If last_row >= 7 Then
        Range("A7:K" & last_row).ClearContents
    End If
    
    Application.EnableEvents = False

    If Target.Address(0, 0) = "B3" Then
        Range("G3") = Empty
    
        With Target
            num = Replace(CStr(.Value), ".", "")
            num = Replace(num, "-", "")
            num = Replace(num, " ", "")
        End With
        
        With Sheets("Data")
            Set fnd = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Find( _
                What:=num, _
                LookIn:=xlValues)
        End With
        
        If Not fnd Is Nothing Then
            Range("A7") = fnd
            Range("C7") = fnd.Offset(, 1)
            Range("H7") = fnd.Offset(, 2)
        End If
    End If
    
    If Target.Address(0, 0) = "G3" Then
        Range("B3") = Empty
    
        With Sheets("Data")
            Set fnd = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Find( _
                What:=Target, _
                LookIn:=xlValues, _
                LookAt:=xlPart)
        End With
        
        If Not fnd Is Nothing Then
            first_address = fnd.Address
            
            Do
                With Sheets("Data")
                    Set fnd = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).FindNext(fnd)
                End With
                
                If Not fnd Is Nothing Then
                    last_row = Cells(Rows.Count, "A").End(xlUp).Row
                    
                    Range("A" & last_row).Offset(1) = fnd.Offset(, -1)
                    Range("C" & last_row).Offset(1) = fnd
                    Range("H" & last_row).Offset(1) = fnd.Offset(, 1)
                End If
            Loop While fnd.Address <> first_address
        End If
    End If
    
    Application.EnableEvents = True
    
End Sub