Select All Rows Sharing Value in one Column, after finding different value in another

[ChristiaanV]

I have a spreadsheet where I'd like to find records based on a value in one column, then select all records based on the values in a second column so I can drill down. Is this possible?

For instance, see the example attached. I'd like to find all instances where the "Tender" was "Visa", then select all records with the same account numbers - regardless if the tender was Visa or not. My goal is to find all accounts records where a Visa was used on at least instance.

Thanks.

Excel Example.PNG

[CK76]

Available method/tools will depend on your Excel version. Please update your profile with Excel version that you use.

[ChristiaanV]

Available method/tools will depend on your Excel version. Please update your profile with Excel version that you use.

Done (Excel 2013 64-bit)

[CK76]

If you can use vba I'd do it like below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ar
If Not Intersect(Target, [A2]) Is Nothing Then
    If Len(Target.Value) = 0 Then
        ListObjects(1).AutoFilter.ShowAllData
    Else
        ar = ListObjects(1).DataBodyRange.Value
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(ar)
                If ar(i, 3) = Target.Value Then
                    .Item(CStr(ar(i, 1))) = 1
                End If
            Next
            ListObjects(1).Range.AutoFilter 1, .Keys, 7
        End With
            
    End If
End If
    
End Sub

See attached.

If you have access to PowerQuery, there are other options.

[ChristiaanV]

Thanks! And thank you for introducing me to Power Query - I'm going to be looking into that.