Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a pivot table which resembles a SQL 'group by'
Hi all,
I was hoping you can help me with this question: how do I build a pivot table which looks like a "group by" operation in a database? Let's say I have this table: Continent Product Sales Asia Red 100 Asia Yellow 200 Asia Red 100 Asia Yellow 300 Europe Red 100 Europe Yellow 200 Europe Red 100 Europe Yellow 300 An Excel Pivot table will return this: Row Labels Sum of Total sales Asia 700 Red 200 Yellow 500 Europe 700 Red 200 Yellow 500 Grand Total 1400 whereas a statement like: select Continent, Product, sum(Sales) as TotalSales from MyTable group by Continent, Product in SQL will return: Continent Product Total sales Asia Red 200 Asia Yellow 500 Europe Red 200 Europe Yellow 500 Is there an easy way to obtain the latter in Excel? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a pivot table which resembles a SQL 'group by'
One way -
Create a "new" set of data that translates your 3 columns of data into 2 by concatenating the Continent and Product columns. Assuming Continent is in column A and Product in B, then =A2&" - "&B2 will give you Asia - Red Then build your pivot table using the new, concatenated column instead of the distinct Continent and Product columns " wrote: Hi all, I was hoping you can help me with this question: how do I build a pivot table which looks like a "group by" operation in a database? Let's say I have this table: Continent Product Sales Asia Red 100 Asia Yellow 200 Asia Red 100 Asia Yellow 300 Europe Red 100 Europe Yellow 200 Europe Red 100 Europe Yellow 300 An Excel Pivot table will return this: Row Labels Sum of Total sales Asia 700 Red 200 Yellow 500 Europe 700 Red 200 Yellow 500 Grand Total 1400 whereas a statement like: select Continent, Product, sum(Sales) as TotalSales from MyTable group by Continent, Product in SQL will return: Continent Product Total sales Asia Red 200 Asia Yellow 500 Europe Red 200 Europe Yellow 500 Is there an easy way to obtain the latter in Excel? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a pivot table which resembles a SQL 'group by'
I'm afraid that's not feasible, as I need to change the structure of
my pivots on the fly. In other words, I cannot know from the beginning which fields I will be grouping by, and preparing concatenated fields for all possible combinations doesn't work - it's like killing a mosquito with a nuclear bomb :) I have found a slightly better solution: in Excel 2007: go to design -- report layout -- show in tabular form. Not quite the same result as in SQL, but I guess it's the closest the piece of junk aka as Excel can get! On Aug 7, 1:14*pm, Duke Carey wrote: One way - Create a "new" set of data that translates your 3 columns of data into 2 by concatenating the Continent and Product columns. *Assuming Continent is in column A and Product in B, then =A2&" - "&B2 will give you Asia - Red Then build your pivot table using the new, concatenated column instead of the distinct Continent and Product columns |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table group help | Excel Discussion (Misc queries) | |||
Creating a preato from a group of data choosen from a pivot chart | Excel Discussion (Misc queries) | |||
how to group a pivot table | Excel Discussion (Misc queries) | |||
Pivot Table, can't group | Excel Discussion (Misc queries) | |||
Pivot Table - no group on web | Excel Discussion (Misc queries) |