Pivot table based on logic

[linga]

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

 

[oldbrewer]

you are getting no replies because it is not at all clear - I can see you want 2 rows ending in NM removed (or ignored ?) then anything except DM removed or ignored - then sort by consvalue - really cant see the point......

 

[oldbrewer]

	name1	name2	name3	name4	name5	name6	name7
	XXXX	DM	IBD	BO	3	1000	runner
	XXXX	DM	IBD	BO	7	1000	NM
	XXXX	DM	IBD	BO	11	1000	runner
	XXXX	DM	IBD	BO	15	1000	NM
	XXXX	DM	IBD	BO	19	1000	runner
	XXXX	DM	IBD	BO	23	1000	NM
	XXXX	DM	IBD	BO	27	1000	runner
	XXXX	DM	IBD	BO	31	1000	runner
	XXXX	DM	IBD	BO	35	1000	NM
	XXXX	DM	IBD	BO	39	1000	NP
	XXXX	DM	IBD	BO	43	1000	runner
	XXXX	DM	IBD	BO	47	1000	runner
	XXXX	DM	IBD	BO	51	1000	NP
	XXXX	DM	IBD	BO	55	1000	runner
	XXXX	DM	IBD	BO	59	1000	runner
	XXXX	DM	IBD	BO	63	1000	NP
	XXXX	IDM	IBD	BO	67	1000	runner
	XXXX	IDM	IBD	BO	71	1000	runner
	XXXX	IDM	IBD	BO	75	1000	runner
	XXXX	IDM	IBD	BO	79	1000	runner
	295
	this is all DM's with NM and NP ignored
	name5 column being totalled
=SUMPRODUCT(($C$2:$C$21="DM")*($F$2:$F$21))-SUMPRODUCT(($C$2:$C$21="DM")*($H$2:$H$21="NM")*($F$2:$F$21))-SUMPRODUCT(($C$2:$C$21="DM")*($H$2:$H$21="NP")*($F$2:$F$21))

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

 

[SpillerBD]

you are getting no replies because it is not at all clear - I can see you want 2 rows ending in NM removed (or ignored ?) then anything except DM removed or ignored - then sort by consvalue - really cant see the point......

In addition, I'm not to sure what our original data is either...
With Pivot Tables, you should also include what version of Excel you are using because some solutions in 2013 are great but not available if you are using 2010 or 2007. (Please don't say your on anything older...)

 

[oldbrewer]

i still use excel 2000 and proud of it.........................

 

[SpillerBD]

i still use excel 2000 and proud of it.........................

which is why you're so good with SUMPRODUCT. And nothing wrong with still being on 2000, just might not realize some of the simpler solutions now available.
Though there was some formatting for Pivot Tables in 2000 I preferred, many of the newer features for Pivot Tables and tables far outweigh those minor differences. Let alone the other list of compatibility issues with anything from 2007 or later. So glad to have the "big grid" and not have to split my data exceeding 63k rows.
I also like being able to set up Pivot Tables for others with the "slicers" making filtering much easier for recipients not familiar with the regular filters available.
PS. You know v2016 is available for testing now?

 

[SpillerBD]

I dono how to attach excel which contains data pl guide.

You find some guidance http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html
& http://www.mrexcel.com/forum/excel-questions/127080-guidelines-posting-updated-22-nov-2006-a.html
Generally for actual Workbooks, you would use a file sharing such as GoogleDrive, DropBox or OneDrive to create a link you could share on the forum or PM someone specific.

 

[oldbrewer]

thanks, Spiller I am 69 and not sure I am up to sumifs and slicers.......

 

[linga]

Hi,

Pl refer detailed column wise data for ref.

Plant Material Category Classification Invvalue Consvalue ABC FREQ_IND[/SIZE]
Column1- Plant -XXXX
Column2-Material -1,2,3,etc
Column3-Category -DM/IDM
Column3-Classification - IBDBO/IBD STO/OBDSTO/WIP/NP/NM/OBDSALE
Column4-Inv value -2500
Column5-Cons value -1000
Column5-A/B/C
Column6- FREQ_IND -NM/NP & blank data

Regards,
Linga

 

[oldbrewer]

linga - I think you need to explain in words more clearly

you have 2 column 3's a typo presumably

eg for each category I want to.............


otherwise I cannot help any more..