Combine Specific Columns in Pivot Table

[kenzie]

Hi Everyone:

I have been trying to search this issue for a while with no luck:

I have a list of people with certain classifications and need to "roll-up" some of those classifications into high level groups. For instance, if I have a list of people with their favorite ice cream and the $ they have spent on ice cream. Some like vanilla, some like chocolate, and some like vanilla and chocolate equally.

I want to create a pivot table that shows the people as row items and the amount of $ they have spent on vanilla (one column) and chocolate (second column) ice cream.

So, this is simple for people who like vanilla or chocolate, but not both. My question is: how can I add the total amount of money for vanilla+chocolate people into BOTH the vanilla and chocolate column (i know this wouldn't make sense to add total value to both, but that is what I am trying to do).

I also want the table to remain interactive, so that when I click on the values, it expands into another sheet that shows the details for each person (e.g., how many purchases they made, etc.)

Thank you for your help -- please let me know if this is not descriptive enough.

P.S. - I don't know how to do macros =(

 

[ChrisM]

The easiest way to do this is back in your raw data. You need to create a new column with a higher level grouping, like "Combined Flavors". Then you create a formula in each row to test for certain conditions (ie values in both the chocolate and vanilla columns) and you assign your new group value, like Choc-Vanilla.

Then set your pivot table to include this new column and you should be all set.

 

[kenzie]

Hi Chris,

Thank you so much for your reply. I actually have the table set up this way (with the combined data displayed as its own column). My raw data has the combined classifications listed and this is all displayed in my table. For the intent of my real life example, I only want to show the high level classifications (i.e., only vanilla and chocolate).

I do not know if I can selectively add columns together in the table, so my next thought was if it was possible to create a data table with an IF function: saying that IF a row in my raw data contained "vanilla" add that $ to the vanilla column, etc. That way, it could pick up cells that had Vanilla and Chocolate listed in the cell and add the value equally to both vanilla and chocolate columns.

But, I have no idea if that is possible. Otherwise, I am stuck =/