Data to be processed using VB Macro

[Nitya0808]

Hello All,

There is a set of data in sheet 1.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Unit[/TD]
[TD]Period[/TD]
[TD]Data[/TD]
[TD]Lifecycle[/TD]
[/TR]
</tbody>[/TABLE]


There are 5 different units, and the categories can be similar or different across data. The data needs to categorized across units, per category, per period based on the lifecycle (only Active lifecycle is taken into calculation) in sheet 2.


Sheet 1

Book1
	A	B	C	D	E
1	Category	Unit	Period	Data	Lifecycle
2	A	X	P01	345	Active
3	A	X	P01	678	Active
4	A	Y	P02	32	Mature
5	B	Z	P02	567	Active
6	B	Z	P01	66	Active
7	B	Z	P01	-90	Active
8	C	X	P02	100	Active
9	D	Y	P01	11223	Active
10	A	X	P02	865	Active
11	A	Y	P01	124	Mature
12	C	Z	P02	674	Active
13	C	Z	P02	742	Active
14	C	Z	P01	983	Active
15	D	W	P01	345	Active
16	D	W	P02	742	Active
17	D	W	P02	641	Active
18	A	X	P01	578	Not Applicable
19	B	Y	P01	444	Active
20	C	Y	P02	980	Active
Sheet1

Cell Formulas
Range	Formula
A1	Category
A2	A
A3	A
A4	A
A5	B
A6	B
A7	B
A8	C
A9	D
A10	A
A11	A
A12	C
A13	C
A14	C
A15	D
A16	D
A17	D
A18	A
A19	B
A20	C
B1	Unit
B2	X
B3	X
B4	Y
B5	Z
B6	Z
B7	Z
B8	X
B9	Y
B10	X
B11	Y
B12	Z
B13	Z
B14	Z
B15	W
B16	W
B17	W
B18	X
B19	Y
B20	Y
C1	Period
C2	P01
C3	P01
C4	P02
C5	P02
C6	P01
C7	P01
C8	P02
C9	P01
C10	P02
C11	P01
C12	P02
C13	P02
C14	P01
C15	P01
C16	P02
C17	P02
C18	P01
C19	P01
C20	P02
D1	Data
D2	345
D3	678
D4	32
D5	567
D6	66
D7	-90
D8	100
D9	11223
D10	865
D11	124
D12	674
D13	742
D14	983
D15	345
D16	742
D17	641
D18	578
D19	444
D20	980
E1	Lifecycle
E2	Active
E3	Active
E4	Mature
E5	Active
E6	Active
E7	Active
E8	Active
E9	Active
E10	Active
E11	Mature
E12	Active
E13	Active
E14	Active
E15	Active
E16	Active
E17	Active
E18	Not Applicable
E19	Active
E20	Active

Sheet 2

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	Unit	Category	P01	P02	P03	P04	P05	P06	P07	P08	P09	P10	P11	P12	Total
5	X	A	1,023.00												1,023.00
6		C	865.00	100.00											965.00
7		Total	1,888.00	100.00											1,988.00
8
9
10	W	D	345.00	1,383.00											1,728.00
11		Total	345.00	1,383.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	1,728.00
12
13
14	Y	D	11223.00												11,223.00
15		B	444.00	32.00											476.00
16		C		980.00											980.00
17		Total	11,667.00	1,012.00											12,679.00
18
19
20	Z	B	-24.00	567.00											543.00
21		C	983.00	1,416.00											2,399.00
22		Total	959.00	1,983.00											2,942.00
23
24
25	Overall	A	1023.00	865.00											1,888.00
26		D	11568.00	1383.00											12,951.00
27		B	420.00	567.00											987.00
28		C	983.00	2496.00											3,479.00
29		Total	12,971.00	4,446.00											19,305.00
Sheet2

Cell Formulas
Range	Formula
D7	=SUM(D5:D6)
D11	=D10
D17	=SUM(D14:D16)
D22	=SUM(D20:D21)
D29	=SUM(D26:D28)
E7	=SUM(E5:E6)
E11	=E10
E17	=SUM(E14:E16)
E22	=SUM(E20:E21)
E29	=SUM(E26:E28)
P5	=SUM(D5:O5)
P6	=SUM(D6:O6)
P7	=SUM(P5:P6)
P10	=SUM(D10:O10)
P11	=P10
P14	=SUM(D14:O14)
P15	=SUM(D15:O15)
P16	=SUM(D16:O16)
P17	=SUM(P14:P16)
P20	=SUM(D20:N20)
P21	=SUM(D21:N21)
P22	=SUM(D22:N22)
P25	=SUM(D25:O25)
P26	=SUM(D26:O26)
P27	=SUM(D27:O27)
P28	=SUM(D28:O28)
P29	=SUM(P25:P28)
F11	=F10
G11	=G10
H11	=H10
I11	=I10
J11	=J10
K11	=K10
L11	=L10
M11	=M10
N11	=N10
O11	=O10

 

[Nitya0808]

Hi Mick,

If data is loaded only for P01, the value is calculated only for the first category, it does not give the same output in table form like the Sheet 2 for all units and categories.

If data is loaded for 12 months, then it gives a subscipt out of range error.

Thanks,
Nitya