Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Randall Arnold
 
Posts: n/a
Default 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
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default flattening aggregated fields in pivot table

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

  #3   Report Post  
Randall Arnold
 
Posts: n/a
Default flattening aggregated fields in pivot table

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Fields smck Excel Worksheet Functions 2 October 27th 05 03:39 AM
Pivot Table Cycling Through Page Fields Automatically nrehman Excel Discussion (Misc queries) 14 October 13th 05 07:32 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM
pivot table question, sum fields? Todd L. Excel Worksheet Functions 2 November 30th 04 06:07 AM
Is there an autofill for all row fields in a pivot table? Wendy B Excel Worksheet Functions 2 November 10th 04 02:54 PM


All times are GMT +1. The time now is 04:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"