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

 

[MickG]

Looks like my typo
Add the dot where shown in red.

Set Rng = .Range(.Cells(2, Cl),[COLOR=#ff0000][SIZE=4][B] .[/B][/SIZE][/COLOR]Cells(Rows.Count, Cl).End(xlUp))

 

[Nitya0808]

No Worries... Its resolved now.

Now i am getting a Subscript Out of Range error in the below linw

Ray(c, 1) = K

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

Thanks,
nitya

 

[MickG]

Please send and example of the data it failed on !!!

 

[Nitya0808]

Hi,

It is similar data but has 53648 rows.

Also, not sure if this caused the error, the heading of Subunit changed to BG. Here is the current column headers.

Please let me know if you need me to paste 53648 line of data.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Category	Unit	BU	BL	Market	Market Unit	Year	Period	Data	Corporate	KPI Lifecycle
2019 Actuals

Cell Formulas
Range	Formula
A1	Category
B1	Unit
C1	BU
D1	BL
E1	Market
F1	Market Unit
G1	Year
H1	Period
I1	Data
J1	Corporate
K1	KPI Lifecycle

 

[MickG]

You could sent a file example through "Box.com" or "DropBox.com" or you could try the code on a limited number of rows, increasing the number of rows until you get the error, then whittle it down to a number of lines in and around that error, then send that data. Just the headers and the data will do!!!

 

[MickG]

I just thought, should have done it earlier.!!!
Change the line below where it says 100 to 1000 and try again, if necessary Increase as required. ??

ReDim Ray(1 To 100, 1 To UBound(Hds) + 1)

 

[Nitya0808]

Hi,

Even when i made it to 2000 i got the same error, then i made a change.

In my sheet i had sub-units for all the units, though i wanted only sub-units of 1 unit displayed. When i removed the sub-units of the other units the code worked.

Ex:

Unit X has sub-units AA,BB,CC,DD
Unit Y has sub-units FF,GG,HH

We need to display only AA,BB,CC,DD and ignore rest. Currently when i cleared the sub-unit column of unit Y, the code worked fine.

 

[MickG]

Can you show an small example this post#17 layout.

 

[Nitya0808]

Hi,

I am not sure why, but when i put in the old data, it all worked fine...

There are a couple more actions, i need to do on this sheet, should i post in on the same thread or should i start a new one.

Thanks,
Nitya

 

[MickG]

Keep to this thread else it gets confusing.!!