Search/filter one field but include all values from another

[mindbender]

I have an excel sheet with several columns. 1 column is accessory and another is serial number. The data looks like this

123456 BXA
123456 RGT
098765 BXA
098765 ONL
876543 TYR
876543 RED

I want to search/filter on the second column but include all related items.

If I search.filter for BXA in column 2 I would like the following to return:
123456 BXA
123456 RGT
098765 BXA
098765 ONL

Column 2 is an accessory on the serial number (column 1). So basically I need a way to search for specific accessories but in the results also see any other accessories that are also on that serial number.

Is this even possible?

 

[MickG]

Try this "Selection Change Event" for data in columns A:B and Results in column "D:E".
NB:- Paste Code in Activesheet Code Module, Right click sheet Tab>> Select "View Code">> Paste Code >>. close Code window >> select value from column "B" for results.

[COLOR="Navy"]Sub[/COLOR] MG08May29
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Column = 2 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
Columns("D:E").ClearContents
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = Target.Value [COLOR="Navy"]Then[/COLOR] Temp = Dn.Offset(, -1).Value
           [COLOR="Navy"]If[/COLOR] Dn.Offset(, -1).Value = Temp [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                Cells(c, 4) = Dn.Offset(, -1).Value
                Cells(c, 5) = Dn.Value
           [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mindbender]

Try this "Selection Change Event" for data in columns A:B and Results in column "D:E".
NB:- Paste Code in Activesheet Code Module, Right click sheet Tab>> Select "View Code">> Paste Code >>. close Code window >> select value from column "B" for results.

[COLOR=Navy]Sub[/COLOR] MG08May29
Private [COLOR=Navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=Navy]As[/COLOR] Range)
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]String,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]If[/COLOR] Target.Column = 2 [COLOR=Navy]Then[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
Columns("D:E").ClearContents
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Dn.Value = Target.Value [COLOR=Navy]Then[/COLOR] Temp = Dn.Offset(, -1).Value
           [COLOR=Navy]If[/COLOR] Dn.Offset(, -1).Value = Temp [COLOR=Navy]Then[/COLOR]
                c = c + 1
                Cells(c, 4) = Dn.Offset(, -1).Value
                Cells(c, 5) = Dn.Value
           [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks for the reply. I may have screwed it up implementing it..and I am not sure what the Columns("D:E").ClearContents refers to.

In my spreadsheet column 1 above (the serial number) is column E and column 2 above (the Accessory column) is column O

I edited the code as follows but it I still only get what I filtered on and not all of the other accessories as well. Obviously something I have done.

Sub MG08May29()Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range, Dn As Range, Temp As String, c As Long
If Target.Column = 15 Then
Set Rng = Range(Range("O1"), Range("O" & Rows.Count).End(xlUp))
Columns("D:E").ClearContents
For Each Dn In Rng
    If Dn.Value = Target.Value Then Temp = Dn.Offset(, -1).Value
           If Dn.Offset(, -1).Value = Temp Then
                c = c + 1
                Cells(c, 4) = Dn.Offset(, -1).Value
                Cells(c, 5) = Dn.Value
           End If
    Next Dn
End If
End Sub

 

[MickG]

With your "numbers" in column "E" and you "Letters" in column "O" then try this:-
NB:- This code will hide rows as per your requirement and your selection in column "O".

[COLOR="Navy"]Sub[/COLOR] MG08May24
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Target.Column = 15 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("O1"), Range("O" & Rows.Count).End(xlUp))

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = Target.Value [COLOR="Navy"]Then[/COLOR] Temp = Dn.Offset(, -10).Value
           [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, -10).Value = Temp [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
           [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Hidden = True
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick