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

Pivot Table filters update base on the values from table

J

Jakub

New Member
Hi All,

I have a problem with filtering Pivot table.

My pivot contains information about the car specification like: acceleration from 0-100, fuel consumption, brand, type etc.

I would like to filter the pivot base on the values (in separate table) which will be selected from drop down list for each of the specification.

For example:

I would like to check how many cars are fulfilling the following criteria:

Brand - Toyota
Type - blank
Acceleration 0-100 - 5 sec.
Fuel consumption - 5/100 km

Base on this values the pivot should be filtered.


Do you have any idea how to approach this? Could you please explain it step by step? Does it require the VBA to be included.

I cannot use the slicer as the client doesn't have Excel 2010.


Great thanks in advance.

Regards,
Kuba
 
Hi Kuba,

Since you did not provided a sample file I had made one, see the attached.

Please note I did not found a direct method to do this. The technique shown in file is through a macro. Also note this is just an illustration, if this is what you want the code need to be changed w.r.t. the ranges used in the code.

Regards,
 

Attachments

Hi Somendra,

Thank you very much for your help here. This is what I've wanted to achieve :)

However, how can I reuse your macro for larger number of criteria to filter by?

Lets say that I have 19 criteria by which I can filter-out the pivot. How I should rebuild your macro. I was trying to do this (I have small knowledge about VBA) but I failed.

Regards,
Kuba
 
Hi Kuba,
Does the client have experience with PivotTables that they could filter it themselves? It seems to me, that you have a large amount of initial data with many 19+ attributes. Client needs to be able to narrow down this list. Now, a question at this point, is
(a) are you trying to filter down to results that will be combined somehow (sum/average/etc), or
(b) are you filtering down to all results that match criteria?

If the a, then a PivotTable is the way to go. You can build a basic table, and client should be able to filter directly from there. IMO, the filters are fairly intuitive, but you could certainly add some nice shapes/instructions showing how to use them.

If desire is more like b, then we'll instead want a Filter. We could filter the data in place, or use an Advanced Filter. I would vote for the Adv. Filter, as you can create a dynamic criteria range filled with AND/OR type logic, and have the results copied to a new location. Very handy for "report" type outputs.
http://www.contextures.com/xladvfilter01.html

If you could answer above question, and provide a sample of the raw data you are dealing with, I think that either I or Somendra will be able to help you out better.
 
Hi Luke, first of all thank you very much for explanations and help

So, I've narrowed the criteria to only one which is the brand of the car. I've also used the tips that you have provided.

They were very useful.

Thanks for help once again :)
 
You must log in or register to reply here.
Back
Top