Filtering in Pivot Tables (Excel 2002)

Topic

New Reply

I have a database which I wanted to make interactive to users with varying skills. The data have a hierarchical structure with each key parameter in a separate field. Initially, I used auto filter to isolate the field data required. This is OK and has the advantage that when any field is filtered, all of the other fields are automatically adjusted so that only logically connected data is visible or can be selected.

Then I discovered pivot tables. Although these are brilliant for displaying the data exactly the way I want, I cannot use logical filtering as I could with the auto filter.
I use a pivot table layout which gives the maximum flexibility for grouping the data that I want (see attachment), however, when I choose the main field all of the data in the subfields are still available for selection. Thus, illogical or

Reply | Quote

Replies

If I understand your question,
The autofilter on the pivot table is close to what you want, but doesn’t summarize the data. If you like the autofilter technique you could add insert rows at the the top (above the filter row) and add calculations to Sum the data. You could use SUM to get the total of all the data and you could also include a row that sums (or avergaes, gets min, max, etc) using the SUBTOTAL function. This gets the properties (sum, avg, min, max, etc) of the range of the data that is displayed from a filtered list.

If you want to go the pivot table route with the page fields to be dependent lists (like the autofilters are), there will be some “problems”. This can not be done directly in excel or even really indirectly.

A possible workaround, would be via VB. You would have to create your own comboboxes and place them over the page fields (you still want the page fields). The comboboxes you create would have to determine the list the items to display. You would need to write your own code to loop thru the items in the original dataset and based on what had been chosen in the other lists, create a unique list of (the dependent) items.

John Walkenbach has some code which can be easily adapted (he fills a listbox instead of a combobox) to help work thru the list to get the unique items

Once you select from the appropriate combobox, the value should be fed via the macro into the appropriate page field.

I haven’t worked on this, but the coding might be easier to fill the comboboxes with the list from the data column of the pivot table itself (much less coding, and probably faster than looping thru the entire data list yourself). When you select from the filled combobox you create, it would place the value in the appropriate page field, and when you go to the next combobox it would read the list from the table again.

If you need more details, let us know. I hope this helps,

Steve

Reply | Quote

Many thanks for your prompt response. You have certainly given me a few options to try. The VBA code example might just be the route I need to take. I will try out the suggestions and if I get stuck I’ll get in touch.

Cheers

Reply | Quote

Just as a BTW to your reply. I came across a neat way of extracting unique items from a multiple list some time ago. It does not use any VBA code but exploits arrray formulae. I include an example in case anyone might be interested. All credit due to the original author who I can no longer find unfortunately.
I am attaching an example of how this works.
Thanks

Reply | Quote

Some things to be aware of with array formulas:
They can make the spreadsheet very sluggish if you list is large (they are very calculation intensive, each of your formulas is equivalent to over 100 formulas since each array formula calc for each item in the entire list). Also every time you add an item to your list or edit an item, every single one of the formulas will have to be updated, whether the value changes or not.
The list of formulas must be large enough to contain all the unique items. This may become problematic if you want the list to be “living” as you add more and more items to the database

In your case you will also need to have multiple unique lists with (additional criteria) which most likely would require even more formulas. You have to look at all the selection already and “filter out” the unique items that are not possible based on the selections.

IN your case I would recommend the VB route, especially since you will have an event to trigger when you need to do the “extraction” it does not have to always be “live”. Also if you extract the “unique list” from the pivot table, the code will be faster since it does not need to “prefilter” the list: the pivot has done that for you. You just need to extract the unique items from a column in the pivot table.

Steve

Reply | Quote

Some things to be aware of with array formulas:
They can make the spreadsheet very sluggish if you list is large (they are very calculation intensive, each of your formulas is equivalent to over 100 formulas since each array formula calc for each item in the entire list). Also every time you add an item to your list or edit an item, every single one of the formulas will have to be updated, whether the value changes or not.
The list of formulas must be large enough to contain all the unique items. This may become problematic if you want the list to be “living” as you add more and more items to the database

In your case you will also need to have multiple unique lists with (additional criteria) which most likely would require even more formulas. You have to look at all the selection already and “filter out” the unique items that are not possible based on the selections.

IN your case I would recommend the VB route, especially since you will have an event to trigger when you need to do the “extraction” it does not have to always be “live”. Also if you extract the “unique list” from the pivot table, the code will be faster since it does not need to “prefilter” the list: the pivot has done that for you. You just need to extract the unique items from a column in the pivot table.

Steve

Reply | Quote

Just as a BTW to your reply. I came across a neat way of extracting unique items from a multiple list some time ago. It does not use any VBA code but exploits arrray formulae. I include an example in case anyone might be interested. All credit due to the original author who I can no longer find unfortunately.
I am attaching an example of how this works.
Thanks

Reply | Quote