• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple column filter against grouped rows in Pivot

Rahul Kanadia

New Member
I have a dataset that looks like so:

TitleActivity 1Activity 2Activity 3
T1918760
T283580

I have made multiple pivots that present data as so:

Activity 1T1T2
0 - 101
11 - 20
21 - 30
31 - 40
41 - 50
51 - 60
61 - 70
71 - 80
81 - 90
91 - 1001

I am trying to get the following result which can very easily be achieved with countifs but it would not show the filtered data like pivot does:

T1T1T1T2T2T2
Value GroupActivity 1Activity 2Activity 3Activity 1Activity 2Activity 3
0 - 101
11 - 20
21 - 30
31 - 401
41 - 50
51 - 601
61 - 70
71 - 801
81 - 901
91 - 1001

The activities are independent of each other.
I am working on VBA for double click and filter. Would love to know if anyone has ideas on doing this with slicers or pivots.
Unable to share the reference data since office systems are encrypted.

Please and thank you...
 
Rahul Kanadia
Your I am working on VBA for double click and filter.
Are You asking Ask an Excel Question - solution?
... You've opened Your thread there.
Your Unable to share the reference data since office systems are encrypted.
Could You create some sample data, which shows Your needs as well as expected results?
 
You are trying to pivot data that is already pivoted. You need to unpivot that table of yours so that it looks something like this:
83373
and pivot that.

In that attached, rhe unpivotting is carried out in the background by Power Query, whose output is to a pivot table:

83374
 

Attachments

  • Chandoo51779.xlsx
    19.3 KB · Views: 3
Thank you. Appreciate your taking the time to respond.
While your solution is something i already tried, it would have to be an added step to refresh the tables themselves. That's not even considering, not all users will have the Excel version needed to run power queries.
I have gone with countifs-ed table with double click to filter unique IDs from interim data, and display a filtered sheet from the actual source data for those IDs.
 
Back
Top