Pivot Table Madness

[mike montagne]

Geesh. I can't seem to get this...

I have this table not reliably sorted:

Job Number Employee Week1 Week2 Week3
0001 Fred 4 4 4
0001 Barney 8 8 8
0002 Wilma 4 4 4
0003 Betty 4 4 4
0001 BamBam 8 8 8

And I need this:

Job Number Employee Week1 Week2 Week3
0001 Fred 4 4 4
Barney 8 8 8
BamBam 8 8 8
Total 20 20 20
0002 Wilma 4 4 4
Total 4 4 4
0003 Betty 4 4 4
Total 4 4 4

Basically I need my table grouped by job number thensorted by employee
showing the hours assigned per week. Is this a job for pivot tables? How?

thanks,
-mjm

 

[Dave Peterson]

If your data looked more like:

Job number Employee Week# Hours
0001 Fred 1 4
0001 Fred 2 4
0001 Fred 3 4
....

I think a pivottable would work nicely.

But as it sits, I think Data|Subtotal would get you very close to what you want.

You can use some conditional formatting techniques at Debra Dalgleish's site to
hide the duplicated job numbers:
http://www.contextures.com/xlCondFormat03.html#Duplicate

 

[Dave Peterson]

Actually, your data looks fine.

Select your range.
data|pivottable
follow the wizard until you get to the dialog with the Layout button
hit that layout button.

Drag Job number to the row area
drag employee to the row area
drag week1 to the data area
drag week2 to the data area
drag week3 to the data area

Each of the Week#'s should show "Sum of". If any don't, then double click on
those and change it to "sum".

Then finish up the wizard.

Now drag the Data button one cell to the right. Drop it directly on the cell
that says Total.

(Sorry about that data|subtotal response--that has to have the data sorted
nicely, too. The pivottable solution doesn't.)