Thread: flattening aggregated fields in pivot table

I'm trying to build what *should* be a simple pivot table with 2 fields and
having some difficulty.

The problem is that Excel wants to automatically aggregate like values. For
instance, the two fields are Product Family and Part Number. There are cases
where 2 different Product Families can share a Part Number (don't ask). In
these few instances, The pivot table wizard automatically groups the 2
instances and only lists the Part Number once; in the second instance, it
generates a blank cell where the PN should go. This wreaks havoc on one of
my macros that needs every single instance listed, ie, no empty cells.

I've gone over and over the pivot table settings and can't find a way to
flatten this or force Excel to understand I don't want these situations
aggregated in the first place. Can't find help on or offline either. Any
experts here have a suggestion?

Thanks,

Randall Arnold

In your source data, you could create a new field that combines the Part
Number and Product Family. Use a formula to combine the values in the
two columns, e.g.:

=A2 & " - " & B2

Then, add that field to the pivot table, instead of the two separate fields.

Randall Arnold wrote:
> I'm trying to build what *should* be a simple pivot table with 2 fields and
> having some difficulty.
>
> The problem is that Excel wants to automatically aggregate like values. For
> instance, the two fields are Product Family and Part Number. There are cases
> where 2 different Product Families can share a Part Number (don't ask). In
> these few instances, The pivot table wizard automatically groups the 2
> instances and only lists the Part Number once; in the second instance, it
> generates a blank cell where the PN should go. This wreaks havoc on one of
> my macros that needs every single instance listed, ie, no empty cells.
>
> I've gone over and over the pivot table settings and can't find a way to
> flatten this or force Excel to understand I don't want these situations
> aggregated in the first place. Can't find help on or offline either. Any
> experts here have a suggestion?
>
> Thanks,
>
> Randall Arnold


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra, but I can't do that either for other reasons.

I wound up using Microsoft Query to grab the data from a SQL server database
in exactly the format I want. Problem solved-- although it would still be
nice if MS had provided a way to do what I wanted in the first place.

Randall

"Debra Dalgleish" wrote:

> In your source data, you could create a new field that combines the Part
> Number and Product Family. Use a formula to combine the values in the
> two columns, e.g.:
>
> =A2 & " - " & B2
>
> Then, add that field to the pivot table, instead of the two separate fields.
>
> Randall Arnold wrote:
> > I'm trying to build what *should* be a simple pivot table with 2 fields and
> > having some difficulty.
> >
> > The problem is that Excel wants to automatically aggregate like values. For
> > instance, the two fields are Product Family and Part Number. There are cases
> > where 2 different Product Families can share a Part Number (don't ask). In
> > these few instances, The pivot table wizard automatically groups the 2
> > instances and only lists the Part Number once; in the second instance, it
> > generates a blank cell where the PN should go. This wreaks havoc on one of
> > my macros that needs every single instance listed, ie, no empty cells.
> >
> > I've gone over and over the pivot table settings and can't find a way to
> > flatten this or force Excel to understand I don't want these situations
> > aggregated in the first place. Can't find help on or offline either. Any
> > experts here have a suggestion?
> >
> > Thanks,
> >
> > Randall Arnold
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>