Filter results according to a list of values generated from another filter

[Clearly]

Hi, I'm finding it difficult to describe exactly what I'm looking to do (which may be why I haven't found a solution) but I think that the example I give below is relatively straightforward to understand.

I have a list of fruit in column A and in column B I have a list of the names the eat that fruit in a many:many relationship (there are also many other columns but I'll leave them out for the sake of simplicity) e.g.

Banana Peter
Banana James
Banana Laurie
Orange Laurie
Orange James
Apple Peter
Apple Paul


I'm trying to work out how I can see all of the rows that contain information about the fruit that one person eats so if I were to look at Peter for example I want to see all of the rows for 'Banana' and 'Apple' i.e.

Banana Peter
Banana James
Banana Laurie
Apple Peter
Apple Paul

I'm more accustomed to SQL (the dataset is generated from a SQL server) and this would a fairly straightforward subquery however I'm being asked if I can present this in Excel and although I have used excel quite a bit it's been at a relatively shallow level (index-match, recorded but not edited macros etc.).

I suspect that a macro could be written to do the job but I don't really know what I'm doing with macros and I wonder if there isn't another way. The dataset isn't huge at maybe 10,000 rows if that makes a difference.

I'm using Windows 7 and Microsoft Office Professional Plus 2013.

I'd be very grateful for any help. I've spent the last 6 hours considering this without really getting anywhere.

 

[MickG]

Try this:-
Data in column A & B.
Name i.e "Peter" in "C1"
Results in columns "D & E".

[COLOR=navy]Sub[/COLOR] MG20Jun21
[COLOR=navy]Dim[/COLOR] Ray [COLOR=navy]As[/COLOR] Variant, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R() [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] Nam [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant, Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
Ray = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
Nam = Range("C1").Value
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] Ac = 1 To UBound(Ray, 2)
  [COLOR=navy]For[/COLOR] n = 1 To UBound(Ray, 1)
     [COLOR=navy]If[/COLOR] Ac = 1 [COLOR=navy]Then[/COLOR] Temp = Ray(n, 2) Else Temp = Ray(n, 1)
       [COLOR=navy]If[/COLOR] Not .Exists(Ray(n, Ac)) [COLOR=navy]Then[/COLOR]
             ReDim R(1): R(1) = Temp
            .Add Ray(n, Ac), R
       [COLOR=navy]Else[/COLOR]
           Q = .Item(Ray(n, Ac))
              ReDim Preserve Q(UBound(Q) + 1)
              Q(UBound(Q)) = Temp
          .Item(Ray(n, Ac)) = Q
      [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Dim[/COLOR] nR [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nS [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
ReDim nRay(1 To UBound(Ray, 1) * 2, 1 To 2)
[COLOR=navy]For[/COLOR] nR = 1 To UBound(.Item(Nam))
    [COLOR=navy]For[/COLOR] nS = 1 To UBound(.Item(.Item(Nam)(nR)))
        c = c + 1
        nRay(c, 1) = .Item(Nam)(nR)
        nRay(c, 2) = .Item(.Item(Nam)(nR))(nS)
    [COLOR=navy]Next[/COLOR] nS
[COLOR=navy]Next[/COLOR] nR
[COLOR=navy]End[/COLOR] With
Range("D1").Resize(c, 2) = nRay
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick