Use Subtotal Function

[TJN]

I have several columns of data and I would like to create subtotals based on
one of the columns. That is easy enough to do, but eventually I would like
to show only the subtotals (and hide the rows making up the subtotal) and
then copy the subtotals to a different sheet to be compared to some other
data. The problem I am having is that Excel puts the subtotal on the row
beneath the data. When I hide the rows making up the subtotal, I lose a
piece of information I need. Let me show you:
Entity # Acct # Amount
456 1020 100.00
456 1020 350.00
456 1020 200.00
456 2100 225.00
560 1025 200.00
560 1025 100.00

When I run the subtotal function, I get
Entity # Acct # Amount
456 1020 100.00
456 1020 350.00
456 1020 200.00
Subtotal 1020 650.00
456 2100 225.00
Subtotal 2100 225.00
560 1025 200.00
560 1025 100.00
Subtotal 1025 300.00

If I hide all but the subtotal rows, I lose the acct number. Does anyone
know of a way to get the 456 and 560 to copy down to the cell on the same row
as the subtotal so when I hide those rows, I still know what account the
subtotal refers to?

Thanks

 

[Matt Lunn]

Hi TJN,

Not sure what you mean here...

"Does anyone
know of a way to get the 456 and 560 to copy down to the cell on the same row
as the subtotal so when I hide those rows, I still know what account the
subtotal refers to?"

456 and 560 are your entity numbers. Subtotal is actually showing you a
summary by account number. Subtotal 1020 shows the sum aof all 1020's etc....

Please post back.

Thanks,
Matt

 

[TJN]

My apologies. I mean the entity number disappears when I show only the
subtotals. I need to somehow keep the entity number so that when I copy the
subtotal rows to a different worksheet to compare to some other data, I know
which entity the subtotal refers to.

Thanks for your help.

Tim

 

[Jim Thomlinson]

Subtotals are not going to automatically want to do something like that for
you. There are a couple of possible solutions...

On way is to paste a formual into all of the blank cells refering to the
cell above. In One of the blank cells (below 456 for instance) add the
formula =A4. Copy this Cell. Select the data from the to bottom of the data
to the top (Say A4:A50). Hit F5 -> Special -> Blanks (all the blank cell will
be selected) and paste the formula. Now Hilight all of column A and Copy ->
Paste Speacial (values).

Another probably easier way is to use a pivot table. Place the cursor in the
data (doesn't need to be sorted) and select data -> Pivot Table. Follow the
wizard and put the table in a new sheet. Add the acct and Entity (Account to
the left of entity) to the right column and the amounts to the middle. It
will look something like this..

Sum of Amount
Acct # Entity # Total
1020 456 650
1025 560 300
2100 456 225
Grand Total 1175

 

[TJN]

Thanks. I'll give it a shot.

Subtotals are not going to automatically want to do something like that for
you. There are a couple of possible solutions...

On way is to paste a formual into all of the blank cells refering to the
cell above. In One of the blank cells (below 456 for instance) add the
formula =A4. Copy this Cell. Select the data from the to bottom of the data
to the top (Say A4:A50). Hit F5 -> Special -> Blanks (all the blank cell will
be selected) and paste the formula. Now Hilight all of column A and Copy ->
Paste Speacial (values).

Another probably easier way is to use a pivot table. Place the cursor in the
data (doesn't need to be sorted) and select data -> Pivot Table. Follow the
wizard and put the table in a new sheet. Add the acct and Entity (Account to
the left of entity) to the right column and the amounts to the middle. It
will look something like this..

Sum of Amount
Acct # Entity # Total
1020 456 650
1025 560 300
2100 456 225
Grand Total 1175