L
Len
Hi,
I have a worksheet that contains about 1200 rows or more and 20
columns , out of which there are a number of groups ( say 34 groups )
carries it own sub-header. Under one group sub-header, it may have 2
or 10 or 50 or even 120 rows which distribute the data across 20
columns. Difficulty in data sorting as each group header has it own
set of transactions based on their running dates. Thus, how to set a
subtotal function for the specific columns under each group in this
scenario.
Example,
A B C D
H I J K L
Date Trans. No Reference No Debit PO Cost None PO cost Travelling
Accom Entertainment
61600.022 COC-04-001 & 04-002
19/03/2008 APD8-01024 00952001 1,258.00 1,258.00
21/03/2008 APD8-00990 9681 500.00 500.00
25/03/2008 APD8-01354 EKL-I-08/21121 47.93 47.93
10/04/2008 APD8-01218 3127 720.00 720.00
23/04/2008 GLP8-01411 MBB0804/4f-2 658.70 658.70 658.70
03/06/2008 GLP8-01950 MBB0806/1f-1 877.20 877.20 877.20
61620 COC-05-001 ABC Ltd
28/02/2008 GLP8-00682 MBB036759(P) 110.00 110.00 110.00
61637 COC-05-015 XYZ plc
01/01/2008 APD8-00627 50043 9,366.65 9,366.65
09/01/2008 APD8-00121 6741 952.00 952.00
09/01/2008 APD8-00214 9610 120.00 120.00
61638 COC-05-020 BBCC ltd
01/01/2008 APD8-00628 I0712012 306.00 306
01/01/2008 APD8-00629 4075 207.71 207.71
01/01/2008 APD8-00631 4087 20,428.72 20428.72
08/01/2008 APD8-00038 NIV-08010306 47.00 47
08/01/2008 APD8-00300 EKL-I-08/20833 174.11 174.11
09/01/2008 APD8-00079 UI801092 482.40 482.4
12/01/2008 APD8-00004 KUL690158 294.21 294.21
18/01/2008 APD8-00332 CTIV00010692 1,377.00 1,377.00 1,377.00
1,377.00
21/01/2008 GLP8-00218 MBB0801/3a-1 8,660.90 8,660.90 8,660.90
8,660.90
31/01/2008 APD8-01178 INV002065227 2,100.00 2100
31/01/2008 JNL08-0016 JNL07-0491 -
12/02/2008 APD8-00680 CTIV00011406 235.00 235.00 235.00
235.00
15/02/2008 APD8-00376 VE8P0095 1,165.50 1165.5
15/02/2008 GLP8-00686 MBB0802/1b-1 283.25 283.25 -
283.25
15/02/2008 GLP8-00686 MBB0802/1b-1 665.30 665.3 665.3 665.3
In the above scenario, the subtotal function for column H to L under
the following group sub-header will be : -
a) Column H I J L
61600.022 COC-04-001 & 04-002 2,525.93
1,535.90 1,535.90 0.00
b)
61620 COC-05-001 ABC Ltd 0.00
110.00 110.00 0.00
c)
61637 COC-05-015 XYZ plc 10,438.65
0.00 0.00 0.00
d)
61638 COC-05-020 BBCC ltd 25,205.65 11,221.45
10,938.20 283.25
Please help how to set subtotal function for the above scenario,
thanks
Regards
Len
I have a worksheet that contains about 1200 rows or more and 20
columns , out of which there are a number of groups ( say 34 groups )
carries it own sub-header. Under one group sub-header, it may have 2
or 10 or 50 or even 120 rows which distribute the data across 20
columns. Difficulty in data sorting as each group header has it own
set of transactions based on their running dates. Thus, how to set a
subtotal function for the specific columns under each group in this
scenario.
Example,
A B C D
H I J K L
Date Trans. No Reference No Debit PO Cost None PO cost Travelling
Accom Entertainment
61600.022 COC-04-001 & 04-002
19/03/2008 APD8-01024 00952001 1,258.00 1,258.00
21/03/2008 APD8-00990 9681 500.00 500.00
25/03/2008 APD8-01354 EKL-I-08/21121 47.93 47.93
10/04/2008 APD8-01218 3127 720.00 720.00
23/04/2008 GLP8-01411 MBB0804/4f-2 658.70 658.70 658.70
03/06/2008 GLP8-01950 MBB0806/1f-1 877.20 877.20 877.20
61620 COC-05-001 ABC Ltd
28/02/2008 GLP8-00682 MBB036759(P) 110.00 110.00 110.00
61637 COC-05-015 XYZ plc
01/01/2008 APD8-00627 50043 9,366.65 9,366.65
09/01/2008 APD8-00121 6741 952.00 952.00
09/01/2008 APD8-00214 9610 120.00 120.00
61638 COC-05-020 BBCC ltd
01/01/2008 APD8-00628 I0712012 306.00 306
01/01/2008 APD8-00629 4075 207.71 207.71
01/01/2008 APD8-00631 4087 20,428.72 20428.72
08/01/2008 APD8-00038 NIV-08010306 47.00 47
08/01/2008 APD8-00300 EKL-I-08/20833 174.11 174.11
09/01/2008 APD8-00079 UI801092 482.40 482.4
12/01/2008 APD8-00004 KUL690158 294.21 294.21
18/01/2008 APD8-00332 CTIV00010692 1,377.00 1,377.00 1,377.00
1,377.00
21/01/2008 GLP8-00218 MBB0801/3a-1 8,660.90 8,660.90 8,660.90
8,660.90
31/01/2008 APD8-01178 INV002065227 2,100.00 2100
31/01/2008 JNL08-0016 JNL07-0491 -
12/02/2008 APD8-00680 CTIV00011406 235.00 235.00 235.00
235.00
15/02/2008 APD8-00376 VE8P0095 1,165.50 1165.5
15/02/2008 GLP8-00686 MBB0802/1b-1 283.25 283.25 -
283.25
15/02/2008 GLP8-00686 MBB0802/1b-1 665.30 665.3 665.3 665.3
In the above scenario, the subtotal function for column H to L under
the following group sub-header will be : -
a) Column H I J L
61600.022 COC-04-001 & 04-002 2,525.93
1,535.90 1,535.90 0.00
b)
61620 COC-05-001 ABC Ltd 0.00
110.00 110.00 0.00
c)
61637 COC-05-015 XYZ plc 10,438.65
0.00 0.00 0.00
d)
61638 COC-05-020 BBCC ltd 25,205.65 11,221.45
10,938.20 283.25
Please help how to set subtotal function for the above scenario,
thanks
Regards
Len