Dear all,
The data is attached in sheet1 and logic is explained inside. my requirement is in pivot when choosing the respective plants
ABC needs to be calculated in sheet1 dynamically and pivot should show the data correspondingly.
I dono how to attach excel which contains data pl guide.
Regards,
Linga
Data
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 DM OBD STO 4000 1300
XXXX 5 DM IBDIMPORTS 4500 1400 NM
XXXX 6 DM IBDIMPORTS 5000 1500 NM
XXXX 7 DM WIP 5500 1600
XXXX 8 DM WIP 6000 1700
XXXX 9 DM OBD SALE 6500 1800
XXXX 10 DM OBD SALE 7000 1900
XXXX 11 IDM IBD BO 7500 2000
XXXX 12 IDM IBD BO 8000 2100
XXXX 13 IDM OBD STO 8500 2200 NP
XXXX 14 IDM OBD STO 9000 2300
XXXX 15 IDM IBDIMPORTS 9500 2400 NP
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 17 IDM WIP 10500 2600 NP
XXXX 18 IDM WIP 11000 2700
XXXX 19 IDM OBD SALE 11500 2800
XXXX 20 IDM OBD SALE 12000 2900
Step 1 Remove NM, NP data In FREQ_IND Column from above data.
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 DM OBD STO 4000 1300
XXXX 7 DM WIP 5500 1600
XXXX 8 DM WIP 6000 1700
XXXX 9 DM OBD SALE 6500 1800
XXXX 10 DM OBD SALE 7000 1900
XXXX 11 IDM IBD BO 7500 2000
XXXX 12 IDM IBD BO 8000 2100
XXXX 14 IDM OBD STO 9000 2300
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 18 IDM WIP 11000 2700
XXXX 19 IDM OBD SALE 11500 2800
XXXX 20 IDM OBD SALE 12000 2900
Step 2 Then Take DM data Only
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 DM OBD STO 4000 1300
XXXX 7 DM WIP 5500 1600
XXXX 8 DM WIP 6000 1700
XXXX 9 DM OBD SALE 6500 1800
XXXX 10 DM OBD SALE 7000 1900
Step 3 Then Descend data based on Cons Value forDM data seperatly.
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 10 DM OBD SALE 7000 1900
XXXX 9 DM OBD SALE 6500 1800
XXXX 8 DM WIP 6000 1700
XXXX 7 DM WIP 5500 1600
XXXX 4 DM OBD STO 4000 1300
XXXX 3 DM OBD STO 3500 1200
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 1 DM IBD BO 2500 1000 RUNNER
Step 4 Calculate Total Sum for Cons Value
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 10 DM OBD SALE 7000 19000
XXXX 9 DM OBD SALE 6500 1890
XXXX 8 DM WIP 6000 1790
XXXX 7 DM WIP 5500 1600
XXXX 4 DM OBD STO 4000 1300
XXXX 3 DM OBD STO 3500 1200
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 1 DM IBD BO 2500 1000 RUNNER
28880
Step 5 Then Calculate % to total For each line item ex: 19000/28700 =66.20
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND % to total
XXXX 10 DM OBD SALE 7000 19000 65.79
XXXX 9 DM OBD SALE 6500 1890 6.54
XXXX 8 DM WIP 6000 1790 6.20
XXXX 7 DM WIP 5500 1600 5.54
XXXX 4 DM OBD STO 4000 1300 4.50
XXXX 3 DM OBD STO 3500 1200 4.16
XXXX 2 DM IBD BO 3000 1100 RUNNER 3.81
XXXX 1 DM IBD BO 2500 1000 RUNNER 3.46
28880
Step 5 "Then Sum the % to total , when the valuereaches to 80 Put Those entries as A,
when the valuereaches to 95 Put Those entries as B,remainig rows as C.
"
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND % to total
XXXX 10 DM OBD SALE 7000 19000 A 65.79
XXXX 9 DM OBD SALE 6500 1890 A 6.54
XXXX 8 DM WIP 6000 1790 A 6.20
XXXX 7 DM WIP 5500 1600 B 5.54
XXXX 4 DM OBD STO 4000 1300 B 4.50
XXXX 3 DM OBD STO 3500 1200 B 4.16
XXXX 2 DM IBD BO 3000 1100 C RUNNER 3.81
XXXX 1 DM IBD BO 2500 1000 C RUNNER 3.46
Step 6 Then Take IDM data Only
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 11 IDM IBD BO 7500 2000
XXXX 12 IDM IBD BO 8000 2100
XXXX 14 IDM OBD STO 9000 2300
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 18 IDM WIP 11000 2700
XXXX 19 IDM OBD SALE 11500 2800
XXXX 20 IDM OBD SALE 12000 2900
Step 7 Then Descend data based on Cons Value for IDM data seperatly w.r.t to respecive plant.
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900
XXXX 19 IDM OBD SALE 11500 2800
XXXX 18 IDM WIP 11000 2700
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 14 IDM OBD STO 9000 2300
XXXX 12 IDM IBD BO 8000 2100
XXXX 11 IDM IBD BO 7500 2000
Step 8 Calculate Total Sum for Cons Value
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900
XXXX 19 IDM OBD SALE 11500 2800
XXXX 18 IDM WIP 11000 2700
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 14 IDM OBD STO 9000 2300
XXXX 12 IDM IBD BO 8000 210
XXXX 11 IDM IBD BO 7500 2000
15410
Step 9 Then Calculate % to total For each line item
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900 18.82
XXXX 19 IDM OBD SALE 11500 2800 18.17
XXXX 18 IDM WIP 11000 2700 17.52
XXXX 16 IDM IBDIMPORTS 10000 2500 16.22
XXXX 14 IDM OBD STO 9000 2300 14.93
XXXX 12 IDM IBD BO 8000 210 1.36
XXXX 11 IDM IBD BO 7500 2000 12.98
15410
Step 10 "Then Sum the % to total , when the valuereaches to 80 Put Those entries as A,
when the valuereaches to 95 Put Those entries as B,remainig rows as C.
"
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900 A 18.82
XXXX 19 IDM OBD SALE 11500 2800 A 18.17
XXXX 18 IDM WIP 11000 2700 A 17.52
XXXX 16 IDM IBDIMPORTS 10000 2500 A 16.22
XXXX 14 IDM OBD STO 9000 2300 B 14.93
XXXX 12 IDM IBD BO 8000 210 B 1.36
XXXX 11 IDM IBD BO 7500 2000 C 12.98
Step 11 Finally group the data
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND % to total
XXXX 10 DM OBD SALE 7000 19000 A 65.79
XXXX 9 DM OBD SALE 6500 1890 A 6.54
XXXX 8 DM WIP 6000 1790 A 6.20
XXXX 7 DM WIP 5500 1600 B 5.54
XXXX 4 DM OBD STO 4000 1300 B 4.50
XXXX 3 DM OBD STO 3500 1200 B 4.16
XXXX 2 DM IBD BO 3000 1100 C RUNNER 3.81
XXXX 1 DM IBD BO 2500 1000 C RUNNER 3.46
XXXX 20 IDM OBD SALE 12000 2900 A 18.82
XXXX 19 IDM OBD SALE 11500 2800 A 18.17
XXXX 18 IDM WIP 11000 2700 A 17.52
XXXX 16 IDM IBDIMPORTS 10000 2500 A 16.22
XXXX 14 IDM OBD STO 9000 2300 B 14.93
XXXX 12 IDM IBD BO 8000 210 B 1.36
XXXX 11 IDM IBD BO 7500 2000 C 12.98
Step 12 Based on Step 11 Pivot will be done
Plant XXXX
Column Labels
Row Labels A B C Grand Total
IBD BO
Sum of Inv value 8000 13000 21000
Sum of Cons Value 210 4100 4310
IBDIMPORTS
Sum of Inv value 10000 10000
Sum of Cons Value 2500 2500
OBD SALE
Sum of Inv value 37000 37000
Sum of Cons Value 26590 26590
OBD STO
Sum of Inv value 16500 16500
Sum of Cons Value 4800 4800
WIP
Sum of Inv value 17000 5500 22500
Sum of Cons Value 4490 1600 6090
Total Sum of Inv value 64000 30000 13000 107000
Total Sum of Cons Value 33580 6610 4100 44290
Step 13 "Our data has multiple plants so if user chooses respective plant
in pivot table the Logic for ""ABC"" needs to be calculated based on sheet 1 dynamically based on above logic which was explained for single plant. "
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 IDM OBD STO 4000 1300
XXXX 5 IDM IBDIMPORTS 4500 1400 NM
XXXX 6 IDM IBDIMPORTS 5000 1500 NM
XXXX 7 IDM WIP 5500 1600
XXXX 8 IDM WIP 6000 1700
XXXX 9 IDM OBD SALE 6500 1800 NM
YYYY 10 DM OBD SALE 7000 1900 NP
YYYY 11 DM IBD BO 7500 2000
YYYY 12 DM IBD BO 8000 2100
YYYY 13 DM OBD STO 8500 2200 NP
YYYY 14 IDM OBD STO 9000 2300
YYYY 15 IDM IBDIMPORTS 9500 2400 NP
ZZZZ 16 IDM IBDIMPORTS 10000 2500
ZZZZ 17 DM WIP 10500 2600 NP
ZZZZ 18 DM WIP 11000 2700
ZZZZ 19 IDM OBD SALE 11500 2800
ZZZZ 20 IDM OBD SALE 12000 2900
The data is attached in sheet1 and logic is explained inside. my requirement is in pivot when choosing the respective plants
ABC needs to be calculated in sheet1 dynamically and pivot should show the data correspondingly.
I dono how to attach excel which contains data pl guide.
Regards,
Linga
Data
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 DM OBD STO 4000 1300
XXXX 5 DM IBDIMPORTS 4500 1400 NM
XXXX 6 DM IBDIMPORTS 5000 1500 NM
XXXX 7 DM WIP 5500 1600
XXXX 8 DM WIP 6000 1700
XXXX 9 DM OBD SALE 6500 1800
XXXX 10 DM OBD SALE 7000 1900
XXXX 11 IDM IBD BO 7500 2000
XXXX 12 IDM IBD BO 8000 2100
XXXX 13 IDM OBD STO 8500 2200 NP
XXXX 14 IDM OBD STO 9000 2300
XXXX 15 IDM IBDIMPORTS 9500 2400 NP
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 17 IDM WIP 10500 2600 NP
XXXX 18 IDM WIP 11000 2700
XXXX 19 IDM OBD SALE 11500 2800
XXXX 20 IDM OBD SALE 12000 2900
Step 1 Remove NM, NP data In FREQ_IND Column from above data.
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 DM OBD STO 4000 1300
XXXX 7 DM WIP 5500 1600
XXXX 8 DM WIP 6000 1700
XXXX 9 DM OBD SALE 6500 1800
XXXX 10 DM OBD SALE 7000 1900
XXXX 11 IDM IBD BO 7500 2000
XXXX 12 IDM IBD BO 8000 2100
XXXX 14 IDM OBD STO 9000 2300
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 18 IDM WIP 11000 2700
XXXX 19 IDM OBD SALE 11500 2800
XXXX 20 IDM OBD SALE 12000 2900
Step 2 Then Take DM data Only
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 DM OBD STO 4000 1300
XXXX 7 DM WIP 5500 1600
XXXX 8 DM WIP 6000 1700
XXXX 9 DM OBD SALE 6500 1800
XXXX 10 DM OBD SALE 7000 1900
Step 3 Then Descend data based on Cons Value forDM data seperatly.
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 10 DM OBD SALE 7000 1900
XXXX 9 DM OBD SALE 6500 1800
XXXX 8 DM WIP 6000 1700
XXXX 7 DM WIP 5500 1600
XXXX 4 DM OBD STO 4000 1300
XXXX 3 DM OBD STO 3500 1200
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 1 DM IBD BO 2500 1000 RUNNER
Step 4 Calculate Total Sum for Cons Value
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 10 DM OBD SALE 7000 19000
XXXX 9 DM OBD SALE 6500 1890
XXXX 8 DM WIP 6000 1790
XXXX 7 DM WIP 5500 1600
XXXX 4 DM OBD STO 4000 1300
XXXX 3 DM OBD STO 3500 1200
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 1 DM IBD BO 2500 1000 RUNNER
28880
Step 5 Then Calculate % to total For each line item ex: 19000/28700 =66.20
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND % to total
XXXX 10 DM OBD SALE 7000 19000 65.79
XXXX 9 DM OBD SALE 6500 1890 6.54
XXXX 8 DM WIP 6000 1790 6.20
XXXX 7 DM WIP 5500 1600 5.54
XXXX 4 DM OBD STO 4000 1300 4.50
XXXX 3 DM OBD STO 3500 1200 4.16
XXXX 2 DM IBD BO 3000 1100 RUNNER 3.81
XXXX 1 DM IBD BO 2500 1000 RUNNER 3.46
28880
Step 5 "Then Sum the % to total , when the valuereaches to 80 Put Those entries as A,
when the valuereaches to 95 Put Those entries as B,remainig rows as C.
"
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND % to total
XXXX 10 DM OBD SALE 7000 19000 A 65.79
XXXX 9 DM OBD SALE 6500 1890 A 6.54
XXXX 8 DM WIP 6000 1790 A 6.20
XXXX 7 DM WIP 5500 1600 B 5.54
XXXX 4 DM OBD STO 4000 1300 B 4.50
XXXX 3 DM OBD STO 3500 1200 B 4.16
XXXX 2 DM IBD BO 3000 1100 C RUNNER 3.81
XXXX 1 DM IBD BO 2500 1000 C RUNNER 3.46
Step 6 Then Take IDM data Only
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 11 IDM IBD BO 7500 2000
XXXX 12 IDM IBD BO 8000 2100
XXXX 14 IDM OBD STO 9000 2300
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 18 IDM WIP 11000 2700
XXXX 19 IDM OBD SALE 11500 2800
XXXX 20 IDM OBD SALE 12000 2900
Step 7 Then Descend data based on Cons Value for IDM data seperatly w.r.t to respecive plant.
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900
XXXX 19 IDM OBD SALE 11500 2800
XXXX 18 IDM WIP 11000 2700
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 14 IDM OBD STO 9000 2300
XXXX 12 IDM IBD BO 8000 2100
XXXX 11 IDM IBD BO 7500 2000
Step 8 Calculate Total Sum for Cons Value
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900
XXXX 19 IDM OBD SALE 11500 2800
XXXX 18 IDM WIP 11000 2700
XXXX 16 IDM IBDIMPORTS 10000 2500
XXXX 14 IDM OBD STO 9000 2300
XXXX 12 IDM IBD BO 8000 210
XXXX 11 IDM IBD BO 7500 2000
15410
Step 9 Then Calculate % to total For each line item
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900 18.82
XXXX 19 IDM OBD SALE 11500 2800 18.17
XXXX 18 IDM WIP 11000 2700 17.52
XXXX 16 IDM IBDIMPORTS 10000 2500 16.22
XXXX 14 IDM OBD STO 9000 2300 14.93
XXXX 12 IDM IBD BO 8000 210 1.36
XXXX 11 IDM IBD BO 7500 2000 12.98
15410
Step 10 "Then Sum the % to total , when the valuereaches to 80 Put Those entries as A,
when the valuereaches to 95 Put Those entries as B,remainig rows as C.
"
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 20 IDM OBD SALE 12000 2900 A 18.82
XXXX 19 IDM OBD SALE 11500 2800 A 18.17
XXXX 18 IDM WIP 11000 2700 A 17.52
XXXX 16 IDM IBDIMPORTS 10000 2500 A 16.22
XXXX 14 IDM OBD STO 9000 2300 B 14.93
XXXX 12 IDM IBD BO 8000 210 B 1.36
XXXX 11 IDM IBD BO 7500 2000 C 12.98
Step 11 Finally group the data
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND % to total
XXXX 10 DM OBD SALE 7000 19000 A 65.79
XXXX 9 DM OBD SALE 6500 1890 A 6.54
XXXX 8 DM WIP 6000 1790 A 6.20
XXXX 7 DM WIP 5500 1600 B 5.54
XXXX 4 DM OBD STO 4000 1300 B 4.50
XXXX 3 DM OBD STO 3500 1200 B 4.16
XXXX 2 DM IBD BO 3000 1100 C RUNNER 3.81
XXXX 1 DM IBD BO 2500 1000 C RUNNER 3.46
XXXX 20 IDM OBD SALE 12000 2900 A 18.82
XXXX 19 IDM OBD SALE 11500 2800 A 18.17
XXXX 18 IDM WIP 11000 2700 A 17.52
XXXX 16 IDM IBDIMPORTS 10000 2500 A 16.22
XXXX 14 IDM OBD STO 9000 2300 B 14.93
XXXX 12 IDM IBD BO 8000 210 B 1.36
XXXX 11 IDM IBD BO 7500 2000 C 12.98
Step 12 Based on Step 11 Pivot will be done
Plant XXXX
Column Labels
Row Labels A B C Grand Total
IBD BO
Sum of Inv value 8000 13000 21000
Sum of Cons Value 210 4100 4310
IBDIMPORTS
Sum of Inv value 10000 10000
Sum of Cons Value 2500 2500
OBD SALE
Sum of Inv value 37000 37000
Sum of Cons Value 26590 26590
OBD STO
Sum of Inv value 16500 16500
Sum of Cons Value 4800 4800
WIP
Sum of Inv value 17000 5500 22500
Sum of Cons Value 4490 1600 6090
Total Sum of Inv value 64000 30000 13000 107000
Total Sum of Cons Value 33580 6610 4100 44290
Step 13 "Our data has multiple plants so if user chooses respective plant
in pivot table the Logic for ""ABC"" needs to be calculated based on sheet 1 dynamically based on above logic which was explained for single plant. "
Plant Material Category Classification Inv value Cons Value ABC FREQ_IND
XXXX 1 DM IBD BO 2500 1000 RUNNER
XXXX 2 DM IBD BO 3000 1100 RUNNER
XXXX 3 DM OBD STO 3500 1200
XXXX 4 IDM OBD STO 4000 1300
XXXX 5 IDM IBDIMPORTS 4500 1400 NM
XXXX 6 IDM IBDIMPORTS 5000 1500 NM
XXXX 7 IDM WIP 5500 1600
XXXX 8 IDM WIP 6000 1700
XXXX 9 IDM OBD SALE 6500 1800 NM
YYYY 10 DM OBD SALE 7000 1900 NP
YYYY 11 DM IBD BO 7500 2000
YYYY 12 DM IBD BO 8000 2100
YYYY 13 DM OBD STO 8500 2200 NP
YYYY 14 IDM OBD STO 9000 2300
YYYY 15 IDM IBDIMPORTS 9500 2400 NP
ZZZZ 16 IDM IBDIMPORTS 10000 2500
ZZZZ 17 DM WIP 10500 2600 NP
ZZZZ 18 DM WIP 11000 2700
ZZZZ 19 IDM OBD SALE 11500 2800
ZZZZ 20 IDM OBD SALE 12000 2900
Last edited:
