Pivot Table Troubles

[Stripe]

Hello, if anyone can help me with this I'd be most grateful.

I have a set of data which is of this format:

Sector1 Stage1 Value£
Sector1 Stage2 Value£
Sector2 Stage1 Value£
Sector2 Stage1 Value£
Sector3 Stage2 Value£
Sector3 Stage2 Value£

Where Sector and Stage are words and Value£ is a pound figure

And I would really like to show it in a table with the following headings:

Sector; Stagex; number of occurances of stagex; Total Value per Stagex

so that each sector and stage are written only once.

i.e.

Sector1 Stage1 1 Value £
(blank) Stage2 1 Value £
Sector2 Stage 1 2 Value £
Sector3 Stage 2 2 Value £

I've tried everything nothing will work at all.

Any suggestions would be most appreciated. Thank you

 

[Cobertizo]

Not sure how far you've got with this, so I'll step through from the beginning:

1. Create table in a sheet (with headings - for my example they are Sector, Stage, Value).
2. Select it all, then go to Data->Pivottable and PivotChart report
3. Hit Next twice.
4. Click on the Options button, and deselect the Grand Total options, and hit OK.
5. Hit Finish.
6. Drag Sector into the "Drop Row Fields Here" bit of the pivot table (left hand side).
7. Drag Stage into the same area (hover over what now lists the sectors, and check from the cursor thing that appears that the Stage bit will be appearing on the right hand side of that column).
8. Drag Value into the "Drop Data Here" bit (bottom right).
9. Drag Stage into the "Drop Data Here" bit too.
10. This will look wrong briefly, but you should see in the third column of the Pivottable that there is a title of "Data". Drag "Data" so it goes on top of "Total", and that should look like the kind of thing you want...
11. If you want to get rid of Subtotals, click in a column, click on Pivottable in the PivotTable toolbar, and untick Subtotal in the drop down.

Hope this helps! There may be an easier way, but this seems to work, if I've understood correctly.

 

[Stripe]

Thank you so much! That worked a treat - you're an absolute lifesafer.

If there's anything I can do to help you out at any stage, please PM me.

Thanks so much again.

Alex