GROUP BY & SUB TOTAL - HOW to do it???
 

I have rows of data as below, I want to group by col 3 & col 4 then sub total
by col 5, how can I do this via macro VBA?

1 2 3 4 5
A 3000 PETER GBP -510.00
B 2500 SARAH USD -1000.00
C 1100 PETER GBP 502.00
D 560 CLAIRE USD 682.00
E 1885 PETER USD 6100.00
F 980 CLAIRE USD -1800.00
G 3220 SARAH EUR 699.00
H 6110 SARAH EUR -500.00

--
Thanks
Sally

GROUP BY & SUB TOTAL - HOW to do it???
 

Instantiate Access copy the data to a table there, build an SQL with GROUPBY
and return the data to Excel... programmatically. That was a joke! :-)

Seriously, you should create a new column concatenating col 3 & col 4, sort
by this new column, then record a macro while you use Tools/Subtotals on that
new column and see what it gives out in VBA. At this point you have the
subtotals, but the source data is still there, interlaced. To remove it, I
think you need to scroll through the list (For Each loop... ) and copy out
all that has a given outline level.

You may also code a Group-by function in VBA

--
Stefano Gatto


"Sally" wrote:

I have rows of data as below, I want to group by col 3 & col 4 then sub total
by col 5, how can I do this via macro VBA?

1 2 3 4 5
A 3000 PETER GBP -510.00
B 2500 SARAH USD -1000.00
C 1100 PETER GBP 502.00
D 560 CLAIRE USD 682.00
E 1885 PETER USD 6100.00
F 980 CLAIRE USD -1800.00
G 3220 SARAH EUR 699.00
H 6110 SARAH EUR -500.00

--
Thanks
Sally

GROUP BY & SUB TOTAL - HOW to do it???
 

I want to end up something like below;
1 2 3 4 5
A 3000 PETER GBP -510.00
C 1100 PETER GBP 502.00
TOTAL = GBP -8.00

E 1885 PETER USD 6100.00
TOTAL = USD 6100.00

F 980 CLAIRE USD -1800.00
D 560 CLAIRE USD 682.00
TOTAL = USD -1118.00

G 3220 SARAH EUR 699.00
H 6110 SARAH EUR -500.00
TOTAL =EUR 199.00

B 2500 SARAH USD -1000.00
TOTAL = USD -1000.00


--
Thanks
Sally


"Stefano Gatto" wrote:

Instantiate Access copy the data to a table there, build an SQL with GROUPBY
and return the data to Excel... programmatically. That was a joke! :-)

Seriously, you should create a new column concatenating col 3 & col 4, sort
by this new column, then record a macro while you use Tools/Subtotals on that
new column and see what it gives out in VBA. At this point you have the
subtotals, but the source data is still there, interlaced. To remove it, I
think you need to scroll through the list (For Each loop... ) and copy out
all that has a given outline level.

You may also code a Group-by function in VBA

--
Stefano Gatto


"Sally" wrote:

I have rows of data as below, I want to group by col 3 & col 4 then sub total
by col 5, how can I do this via macro VBA?

1 2 3 4 5
A 3000 PETER GBP -510.00
B 2500 SARAH USD -1000.00
C 1100 PETER GBP 502.00
D 560 CLAIRE USD 682.00
E 1885 PETER USD 6100.00
F 980 CLAIRE USD -1800.00
G 3220 SARAH EUR 699.00
H 6110 SARAH EUR -500.00

--
Thanks
Sally

GROUP BY & SUB TOTAL - HOW to do it???
 

Hi Sally,

refer to the "article" VBA: Auto Grouping from May 2. in this forum. If
this is not sufficient, write me, I'll give you then detailed support.

Udo

GROUP BY & SUB TOTAL - HOW to do it???
 

I have problem similar to Sally's but I cannot find article to which you refer.
I've tried various combinations of the following but the subtotals "step on
one another":
Selection.Subtotal GroupBy:=3, _
Function:=xlSum, TotalList:=Array(16, 17, 18, 19, 20), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Selection.Subtotal GroupBy:=2, _
Function:=xlSum, TotalList:=Array(16, 17, 18, 19, 20), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Selection.Subtotal GroupBy:=1, _
Function:=xlSum, TotalList:=Array(16, 17, 18, 19, 20), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Thank you for your contributions to this forum.
George


"Udo" wrote:

Hi Sally,

refer to the "article" VBA: Auto Grouping from May 2. in this forum. If
this is not sufficient, write me, I'll give you then detailed support.

Udo