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]

Sure..

Similar to sheet 1, Sheet 3 also contains data but of previous year. That needs to be processed and displayed in Sheet 2, next to the current output.

Sheet 4 makes monthly calculations based on the information in Sheet2.

The Reporting month in Sheet 2 & Sheet 4 is a configurable field from 1 - 12, which will be done manually.

Sample file can be found here.

https://app.box.com/s/ba5l2vf4vd21i4yxfdxiwgkz0a5j5ddx

Thanks in advance for your help.

 

[MickG]

Are you saying the original code is now working OK, based on your original request, because your latest post seems to be a new requirement.???

 

[Nitya0808]

Hi Mick,

Yes. As said in my previous post, these are further enhancements i need on the file. Is it okie to have it here in the same thread?

Thanks,
Nitya

Are you saying the original code is now working OK, based on your original request, because your latest post seems to be a new requirement.???

 

[MickG]

What about:-

Also for displaying Subunits,can we add a condition that we display subunits only when Unit is X.

Ref:- New requirement.
Do you just want the 4 columns on sheet2 starting "S5", but where does that data come from???, they don't appear to match with sheet 3 values.!!

 

[Nitya0808]

What about:-


Ref:- New requirement.
Do you just want the 4 columns on sheet2 starting "S5", but where does that data come from???, they don't appear to match with sheet 3 values.!!

Yes. It comes from Sheet3. It is the Sum of Values from P01 - P12 for Category G in unit W

 

[MickG]

If I run sheet1 code on sheet 3 ,this is what I Get for "P1".

This does not match what you have.!!

 

[MickG]

It is the Sum of Values from P01 - P12 for Category G in unit W

The actual total, I get for the above is :- 164400 ???

 

[Nitya0808]

The actual total, I get for the above is :- 164400 ???

Yes this is the correct total. So Baseline column should have this.

Baseline YTD column = (Baseline/12)*Reporting Period

 

[MickG]

But what about the Units column, your sheet2 columns "S" data reflects the "Units" column for sheets1 results which is not the same as sheet3 "Units" column results.

 

[Nitya0808]

But what about the Units column, your sheet2 columns "S" data reflects the "Units" column for sheets1 results which is not the same as sheet3 "Units" column results.

Hi,

The order of display should be the same for Sheet 1 data and Sheet 3 data, though they might not be in the same order in the respective sheets. Only then the result needed for Sheet 4 can be achieved. If they dont match Sheet 4 calculations will become very complex and confusing.