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

xcellerator

New Member
Joined
Feb 22, 2017
Messages
23
Office Version
  1. 2019
Platform
  1. MacOS
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.

CustomerProductRevenue
BobApple10
BobOrange4
AnnieBanana8
SamApple7
SamFig3
KatieBanana8
KatieGrapes5

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:
CustomerProductRevenue
BobApple10
BobOrange4
SamApple7
SamFig3

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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
with Power Query aka Get&Transform
CustomerProductRevenueCustomerProductRevenue
BobApple10BobApple10
BobOrange4BobOrange4
AnnieBanana8SamApple7
SamApple7SamFig3
SamFig3
KatieBanana8
KatieGrapes5

Power Query:
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
 
Last edited:
Upvote 0
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
ABCDEFGHI
1CustomerProductRevenueFilterCustomerProductRevenue
2BobApple10AppleBobApple10
3BobOrange4BobOrange4
4AnnieBanana8SamApple7
5SamApple7SamFig3
6SamFig3
7KatieBanana8
8KatieGrapes5
Hoja4


Try this:

VBA Code:
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
 
Upvote 0
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-
 
Upvote 0
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).
 
Upvote 0
Why not use Advanced filter
+Fluff New.xlsm
ABCDE
1CustomerProductRevenueApple
2BobApple10TRUE
3BobOrange4
4AnnieBanana8
5SamApple7
6SamFig3
7KatieBanana8
8KatieGrapes5
9
Contacts
Cell Formulas
RangeFormula
E2E2=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,E$1)>0
Named Ranges
NameRefers ToCells
Criteria=Contacts!$E$1:$E$2E2


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
ABCDE
1CustomerProductRevenueApple
2BobApple10TRUE
3BobOrange4
5SamApple7
6SamFig3
9
Contacts
Cell Formulas
RangeFormula
E2E2=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,E$1)>0
Named Ranges
NameRefers ToCells
Criteria=Contacts!$E$1:$E$2E2
 
Upvote 0
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
 
Upvote 0
You will need to set it up like
+Fluff New.xlsm
ABCDEF
1CustomerProductRevenue
2BobApple10TRUEApple
3BobOrange4FALSEGrapes
5SamApple7
6SamFig3
7KatieBanana8
8KatieGrapes5
9
Master
Cell Formulas
RangeFormula
E2E2=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,$F$2)>0
E3E3=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,$F$3)>0
 
Upvote 0

Forum statistics

Threads
1,224,503
Messages
6,179,136
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top