| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summarize table
I need to summarize a table that looks like this:
WBS DIV RES MONTH HOURS DIRECT 1.01.01.01 22 EE1 FEB-05 5 100 1.01.01.01 22 EE1 FEB-05 10 200 1.01.01.01 22 EE1 FEB-05 15 300 1.01.01.01 22 EE1 JUN-05 20 400 1.01.02.01 22 DD1 APR-05 25 500 1.01.02.01 22 PHYS1 FEB-05 30 600 1.01.02.01 22 PHYS1 MAR-05 35 700 into this: WBS DIV RES MONTH HOURS DIRECT 1.01.01.01 22 EE1 FEB-05 30 600 1.01.01.01 22 EE1 JUN-05 20 400 1.01.02.01 22 DD1 APR-05 25 500 1.01.02.01 22 PHYS1 FEB-05 30 600 1.01.02.01 22 PHYS1 MAR-05 35 700 in sql, I would do something like: SELECT WBS, DIV, RES, MONTH, SUM(HOURS) AS HRS, SUM(DIRECT) AS DIR FROM TABLE1 GROUP BY WBS, DIV, RESCODE, MONTH It seems like this could be done with pivot tables, but I'm not familiar enough with them to know where to put each field. Woody |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
summarize table
WBS, DIV, RES, MONTH would be row fields
HOURS and DIRECT would be DATA Fields. Once you create the table, you would probably have Hours Direct Just select the header (Data button) and drag it one cell to the Right, then release. This will put the data in columns instead of rows. -- Regards, Tom Ogilvy "sugargenius" wrote in message oups.com... I need to summarize a table that looks like this: WBS DIV RES MONTH HOURS DIRECT 1.01.01.01 22 EE1 FEB-05 5 100 1.01.01.01 22 EE1 FEB-05 10 200 1.01.01.01 22 EE1 FEB-05 15 300 1.01.01.01 22 EE1 JUN-05 20 400 1.01.02.01 22 DD1 APR-05 25 500 1.01.02.01 22 PHYS1 FEB-05 30 600 1.01.02.01 22 PHYS1 MAR-05 35 700 into this: WBS DIV RES MONTH HOURS DIRECT 1.01.01.01 22 EE1 FEB-05 30 600 1.01.01.01 22 EE1 JUN-05 20 400 1.01.02.01 22 DD1 APR-05 25 500 1.01.02.01 22 PHYS1 FEB-05 30 600 1.01.02.01 22 PHYS1 MAR-05 35 700 in sql, I would do something like: SELECT WBS, DIV, RES, MONTH, SUM(HOURS) AS HRS, SUM(DIRECT) AS DIR FROM TABLE1 GROUP BY WBS, DIV, RESCODE, MONTH It seems like this could be done with pivot tables, but I'm not familiar enough with them to know where to put each field. Woody |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summarize calculated field on pivot table | Excel Discussion (Misc queries) | |||
| pivot table field summarize default | Excel Worksheet Functions | |||
| Summarize times in a pivot table | Excel Worksheet Functions | |||
| Using Pivot Table Function to Summarize | Excel Discussion (Misc queries) | |||
| MS Excel - How can I summarize a table? Will Pivot Table Reports do this for me? | Excel Programming | |||
Hybrid Mode
