Excel: Summarize Data on a new Worksheet? 1

[sagobogger]

How can I generate a spreadsheet that summarizes data? For instance after subtotalling a large spreadsheet I might have >100 subtotal rows and they'll be on rows 451, 522, 1203 etc, and I would like to generate a simple spreadsheet with just the subtotals on rows 1,2,3 etc.

 

[KenWright]

Depending on how you subtotalled, ie was it every change in the data in an index field perhaps, then you should be using Data / Subtotals to do this as it will allow you to collapse the data to the very view you mention. Give us an example of your data.

As an example though:-

Index 1 2 3 4 5 6 7
aa x x x x x x x
aa x x x x x x x
aa x x x x x x x
aa x x x x x x x
bb x x x x x x x
bb x x x x x x x
bb x x x x x x x
bb x x x x x x x

Selecting all this, anmd choosing Data / Subtotals / For every change in Col A, Sum Cols B,C,D,E... etc would produce

Index 1 2 3 4 5 6 7
aa x x x x x x x
aa x x x x x x x
aa x x x x x x x
aa x x x x x x x
STaa y y y y y y y
bb x x x x x x x
bb x x x x x x x
bb x x x x x x x
bb x x x x x x x
STbb y y y y y y y

You then have the option at the press of a button to collapse that to:-

Index 1 2 3 4 5 6 7
STaa y y y y y y y
STbb y y y y y y y

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[sagobogger]

Sorry if my question was poorly phrased, I should have stated that I was using the data/subtotal function. What I am looking for is a way to produce a new worksheet that only contains the summarized data (ie the content of the subtotal rows) and no detail, not a worksheet that contains all the detail and only displays the subtotals which is what the collapse button does.

 

[xlbo]

If you will always have the same groupings and therefore the same no. of subtotalled sections then you can hard code the group identifiers and use SUMIF()

Alternatively, you can just throw a pivottable over the data and view it at any level of detail you desire.

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Or if you don't mind losing the link, collapse your data to the view you want, select all the data, do Edit / Go To / Special / Visible cells only, then copy and paste where you want.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[sagobogger]

Perfect! Thats's exactly what I wanted a simple way to send someone else results without the data

Thanks!