Problem with Subtotal Output!

[mellowe]

Hi All

I have a problem with Subtotals. I have in Col A account numbers in col
D I have Currency Mnenomics. eg. GBP, EUR etc and col F contains amount
values. There is various other information in the other columns but my
key columns are A,D and F

I need to group my data by account group then by Currency with sub
totals after each. Now I have used the subtotal functionality and
achieved this but my problem is with the output layout. Below is what
my output looks like

039259-0 Fund AAAA 9998502 EUR 75,954.90 92,171.27
EUR Total 92,171.27
039259-0 Fund AAAA 9998500 GBP 235,298.12 411,348.17
039259-0 Fund AAAA 9998500 GBP 2,690,131.19 4,702,887.35
039259-0 Total 5,206,406.79
GBP Total 5,114,235.52


I would like to a macro to carry out the Subtotals and then show me in
the format below:

039259-0 Fund AAAA 9998502 EUR Total 92,171.27
039259-0 Fund AAAA 9998500 GBP Total 5,114,235.52
039259-0 Total 5,206,406.79

Is this possible?? As I just cant seem to get what I need and not sure
if Offset would be good to use here? Please help thanks!

 

[Pete_UK]

If you have used Data | Subtotals after sorting the data, you can then
use the group buttons (+ or -) down the left panel to hide or reveal
the details.

An alternative would be to derive unique values of the account numbers
and currency codes in a different sheet then use an array formula or
SUMPRODUCT to derive the subtotals, without needing to sort the
original data. Then you could arrange this summary table in exactly the
format you require.

Hope this helps.

Pete

 

[mellowe]

Thanks for your prompt reply Pete!

The group buttons are no good as when I do this I lose the details in
column B,C,F ( in my example Fund AAA and ref number ).

Sorry to be a bit dumb here, but how do I do the SUMPRODUCT option you
refered to? Is this possible if the data is different every day? I do I
remove the subtotals befire copying to another sheet?

Your help is appreciated!! Thanks!

 

[Pete_UK]

Here's a number of stages:

1. Copy the sheet into the workbook by holding down the CTRL-drag the
sheet tab. Rename this copied sheet to Summary.

2. Remove the subtotals from the Summary sheet by Data | Subtotals and
click Remove All.

3. In the Summary sheet delete all columns beyond the currency code -
this should leave you with columns A to D, and you should give these
appropriate headings in row 1 if you don't already have any (insert new
row if necessary).

4. You want to create a list of unique values from these, so highlight
all the data (including headings) in columns A to D, then click Data |
Filter | Advanced filter, and in the dialogue box choose "Unique
Values" and "Copy to Other Location" - specify $F$1 as the location
then click OK.

5. Delete columns A to E and you have your unique lists - you might
want to adjust the width of the columns, and you can sort these how you
wish the final table to appear.

6. Now you can enter the formula in E2. I am assuming that your
original data occupied A2 to F300 in Sheet1 - adjust the references as
necessary:

=SUM(IF((A2=Sheet1!A$2:A$300)*(D2=Sheet1!D$2$300),Sheet1!F$2:F$300,0))

As this is an array formula, once you have typed it in (or subsequently
edit it) you must use CTRL-SHIFT-ENTER instead of just ENTER. If you do
this correctly, then Excel will wrap curly braces { } around the
formula - you must not type these yourself.

You can then copy this down for as many entries as you have in the
Summary sheet.

You could then use Data | Subtotals to give you overall totals for each
change in account code, though I'm not sure why you would want to add
EUR and GBP together.

Hope this helps.

Pete

 

[mellowe]

Thanks again Pete ...will have a go at this and let you know how it goes

 

[mellowe]

I would like to say thanks for taking the time to help me with this -
I actually used this option on a couple of other things too which
worked a treat ... for my orginal problem I adpated it a bit and it
does nicely ... thanks again.

 

[Pete_UK]

Thanks for the feedback. Solutions are particularly useful if you can
also adapt them to other problems.

Pete