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
21
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.
 

xcellerator

New Member
Joined
Feb 22, 2017
Messages
21
Office Version
  1. 2019
Platform
  1. MacOS
I'm getting closer (and now trying to use XL2BB! :LOL: )
The only part where I am getting stuck is on the output...it shows 2 rows of Katie...but I'm not sure why.

It should be outputting all the purchase rows of any customer that purchased either Apples or Grapes, so...
2 Bob rows (since Bob bought an Apple)
2 Sam rows (since Sam bought an Apple)
2 Katie rows (Since Katie purchased Grapes)

mrexcel-filter.xlsx
ABCDEF
1CustomerProductRevenue
2BobApple10TRUEApple
3BobOrange4FALSEGrapes
4AnnieBanana8
5SamApple7
6SamFig3
7KatieBanana8
8KatieGrapes5
9
10
11
12
13CustomerProductRevenue
14KatieBanana8
15KatieGrapes5
Sheet2
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

xcellerator

New Member
Joined
Feb 22, 2017
Messages
21
Office Version
  1. 2019
Platform
  1. MacOS
Oi.... E2:E3

Corrected and now works. I'll continue to add other criteria in column E, probably up to E20 or so, and then set Criteria as E1:E20 (I had not suspected I needed to include that blank cell).

*MUCH* appreciation Fluff (y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,252
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

xcellerator

New Member
Joined
Feb 22, 2017
Messages
21
Office Version
  1. 2019
Platform
  1. MacOS
Just as reference (for my notes as much as anything)...takes 3 minutes to run for my set (15k rows x 40 columns)
Which is totally fine since I really only need to run it once for each exercise.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,984
Messages
5,621,981
Members
415,871
Latest member
Frank_Aa

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
Top