How to filter on one column, then "lock" a filter on another, then "unlock" the filter on the original

[xcellerator]

I am sure this has been asked and answered here before already, but I'm not even sure what terms to use...as you can tell from my subject header!

I have a table like this - only it is massive...tens of thousands of rows, and dozens of columns.

Customer	Product	Revenue
Bob	Apple	10
Bob	Orange	4
Annie	Banana	8
Sam	Apple	7
Sam	Fig	3
Katie	Banana	8
Katie	Grapes	5

I am looking to filter/pivot/dosomethingelse to get the data to show only the customers that purchased Apples, but also show everything they purchased (so not filter to just Apples).
So it would look like this:

Customer	Product	Revenue
Bob	Apple	10
Bob	Orange	4
Sam	Apple	7
Sam	Fig	3

Then after this my I would want to pivot this table, so I could see the total revenue for these "filtered" Customers (so just Bob and Sam), or the total revenue by Product, etc.

So basically what I would think I would do is:
1. filter Product for Apple
2. then somehow filter Customer for Bob and Sam and keep it locked to just those 2
3. remove Product filter
But I can't figure out how to do what I would think is Step 2.

Maybe I should even be using Pivots for the whole thing...not sure.
Thank you for any advice.

 

[sandy666]

with Power Query aka Get&Transform

Customer	Product	Revenue		Customer	Product	Revenue
Bob	Apple	10		Bob	Apple	10
Bob	Orange	4		Bob	Orange	4
Annie	Banana	8		Sam	Apple	7
Sam	Apple	7		Sam	Fig	3
Sam	Fig	3
Katie	Banana	8
Katie	Grapes	5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TSR = Table.SelectRows(Source, each ([Product] = "Apple")),
    TSC = Table.SelectColumns(TSR,{"Customer"}),
    Join = Table.NestedJoin(TSC,{"Customer"},Source,{"Customer"},"TBL",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "TBL", {"Product", "Revenue"}, {"Product", "Revenue"})
in
    Expand

 

[DanteAmor]

With the following macro for 175 thousand records it takes 2 seconds.
Put the data to filter in cell E2, the results in columns G to I

varios 18sep2020 extrae numeros separados por guion.xlsm
	A	B	C	D	E	F	G	H	I
1	Customer	Product	Revenue		Filter		Customer	Product	Revenue
2	Bob	Apple	10		Apple		Bob	Apple	10
3	Bob	Orange	4				Bob	Orange	4
4	Annie	Banana	8				Sam	Apple	7
5	Sam	Apple	7				Sam	Fig	3
6	Sam	Fig	3
7	Katie	Banana	8
8	Katie	Grapes	5
Hoja4

Try this:

Sub Filter_Data()
  Dim a As Variant, b As Variant
  Dim dic As Object
  Dim i As Long, j As Long
  Dim sFilter As String
  
  a = Range("A2:C" & Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a, 1), 1 To 3)
  Set dic = CreateObject("Scripting.Dictionary")
  sFilter = Range("E2").Value2
  dic.comparemode = vbTextCompare
  
  For i = 1 To UBound(a, 1)
    If LCase(a(i, 2)) = LCase(sFilter) Then
      dic(a(i, 1)) = Empty
    End If
  Next
  
  If dic.Count = 0 Then
    MsgBox "No match"
    Exit Sub
  End If
  
  For i = 1 To UBound(a, 1)
    If dic.exists(a(i, 1)) Then
      j = j + 1
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 2)
      b(j, 3) = a(i, 3)
    End If
  Next
  
  Range("G2").Resize(j, 3).Value = b
End Sub

 

[xcellerator]

Thanks so much. I should have mentioned I am using 'Microsoft Excel for the Mac' (16.xx).
Follow-up I also should have asked -
Is it possible to do this work without VBA? (I'm envisioning using this work with others who will need to be able to change the code...in addition to myself learning)
Thanks again-

 

[sandy666]

I am using 'Microsoft Excel for the Mac' (16.xx).

so update your profile (Account details) about Excel version and OS

 

[xcellerator]

so update your profile (Account details) about Excel version and OS

so I will

 

[xcellerator]

With the following macro for 175 thousand records it takes 2 seconds.
Put the data to filter in cell E2, the results in columns G to I

Thank you Dante.
I have a pretty large number of columns, up to AJ, so I think I need to modify 3 lines of the VBA to AM2 is where I put the filter word, and AO2 is where the data would go...):
a = Range("A2:C" & Range("A" & Rows.Count).End(3).Row).Value2 => a = Range("A2:AJ" & Range("A" & Rows.Count).End(3).Row).Value2
sFilter = Range("E2").Value2 => sFilter = Range("AM2").Value2
Range("G2").Resize(j, 3).Value = b = Range("AO2").Resize(j, 3).Value = b

I receive an error in running the macro, however:
Set dic = CreateObject("Scripting.Dictionary")

Any tips welcome!
Then more broadly, I should have asked if it possible to create multiple filters, such as if someone purchase an Apple *or* an Orange (and then of course only return the result once in the filtered data).

 

[Fluff]

Why not use Advanced filter

+Fluff New.xlsm
	A	B	C	D	E
1	Customer	Product	Revenue		Apple
2	Bob	Apple	10		TRUE
3	Bob	Orange	4
4	Annie	Banana	8
5	Sam	Apple	7
6	Sam	Fig	3
7	Katie	Banana	8
8	Katie	Grapes	5
9
Contacts

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,E$1)>0

Named Ranges
Name	Refers To	Cells
Criteria	=Contacts!$E$1:$E$2	E2

Select a cell in you data, click advanced on the data tab, make sure the List range is correct & then select $E$1:$E$2 for the Criteria range & click ok

+Fluff New.xlsm
	A	B	C	D	E
1	Customer	Product	Revenue		Apple
2	Bob	Apple	10		TRUE
3	Bob	Orange	4
5	Sam	Apple	7
6	Sam	Fig	3
9
Contacts

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,E$1)>0

Named Ranges
Name	Refers To	Cells
Criteria	=Contacts!$E$1:$E$2	E2

 

[xcellerator]

Why not use Advanced filter

Thanks Fluff.
Can this be used for multiple criteria, say if the Customer purchased Apples or Bananas (I'm just interested in "or", and not "and" (at the moment!))?

I tried to stack the Criteria "down", so Apples in E1, Banana in E2, and then update the function, but looks like that isn't the way
Any help is welcome
Thanks again so much, I think this will be just what I'm looking for if it can handle multiple criteria

 

[Fluff]

You will need to set it up like

+Fluff New.xlsm
	A	B	C	D	E	F
1	Customer	Product	Revenue
2	Bob	Apple	10		TRUE	Apple
3	Bob	Orange	4		FALSE	Grapes
5	Sam	Apple	7
6	Sam	Fig	3
7	Katie	Banana	8
8	Katie	Grapes	5
9
Master

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,$F$2)>0
E3	E3	=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,$F$3)>0