Combine Specific Columns in Pivot Table

kenzie

New Member
Joined
Mar 19, 2013
Messages
2
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
Mar 19, 2013
Messages
2
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 =/
 

Forum statistics

Threads
1,082,281
Messages
5,364,219
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top