pivot table display problems

[taigovinda]

Hi,

I'm going to make a lot of pivot tables that have dates in the row field and dollars in each of one to three data fields. Often, one of the data fields will have data from earlier dates than the others. I want to be able to display only the dates where data exists in all three data fields. If any of the three is blank, I want to exclude that date.
...Does anyone have a good way to do this? I can't seem to figure it out, but thinking it would involve using vba to uncheck the display option for a bunch of the row field items. (btw I'm using 2003, I think 2007 might have a display range function..)

Also, I'm not getting the intended effect when I uncheck "show items with no data" in the row field. For example, I am seeing a pivot table with three rows (instead of two, i.e., not displaying the middle row) when I use this for the pivot table area:
date / $$
1/1/1990 / 100
2/1/1990 /
3/1/1990 / 200

I'm beating my head against the wall on this; thanks a lot for any help on either of these two problems.

Tai

 

[PaddyD]

"If any of the three is blank, I want to exclude that date..."

The standard way to exclude specific row data items when one (or more) conditions is met in the data is as follows:

1) in the source data, construct extra column(s) that flag the conditions you're after (e.g. count(range)=3 or whatever's appropriate given your data set up)
2) add the new column as a page field to the pivot
3) set page field to = true (or whatever) to exclude unwanted items

 

[taigovinda]

Thanks for the tip, but I don't think that method will be practical in this case. I will have pivot_data with 50+ columns, and want to quickly create and manipulate multiple pivot tables off of the data. There could be any combination of 2 or 3 of the 50 columns, so excel couldn't possibly give me enough space to add all the columns that would be needed...

The only way I thought of to do this was, as I mentioned, to loop through all the dates in the pivot row field, and for each row field loop through each data field to make sure it's not blank. If any of them are blank, that data field gets unchecked.

So something like this:

<Show all rows in the pivot table>
For each date in <pivot table's rowfield>
For each data point 'if there are three data fields, that would be three points per iteration
If data_point=null then <uncheck the row field of this data point>
Next
Next

A macro like this would enable me to quickly hide all the rows I don't want ...Obviously there's a lot of syntax that is missing there (i.e., all of it), and I don't know how to access a row or data field, or how to reference from one to the other. Hook me up!

Thanks,
Tai

p.s. If anyone has any idea on my second question from the original post, I still haven't figured that one out either!