Power Pivot to get sequence of ingredients

[BDlani]

Have a need to get a list of ingredients we will need based on a sequence list(schedule we will run) and an ingredient list. Here is simple list of data to show the tables I have and the output table based on these tables. I know it could be done with a macro but would prefer to have done with pivot table with calculated field. Have looked at several blogs on this a thought I was close but can not get to work.

[TABLE="width: 244"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]sequence[/TD]
[TD]Line Code[/TD]
[TD]Ordered QTY (ton)[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4200001[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4200002[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4200001[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4200003[/TD]
[TD="align: right"]35
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 272"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Ingredient table
[/TD]
[/TR]
[TR]
[TD]Line Code ingredient Qty per ton
4200001 MILLET 1020.0
4200001 WHEAT 980.0
4200002 MILLET 600.0
4200002 WHEAT 500.0
4200002 STARCH 400.0
4200002 DEXTROSE 300.0
4200002 GELATIN 200.0
4200003 STARCH 1500.0
4200003 WHEAT 250.0
4200003 CARROT 250.0

This is what i want the output to be.
[TABLE="width: 631"]
<colgroup><col><col><col><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 631"]
<colgroup><col><col><col><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD]sequence[/TD]
[TD]Line Code[/TD]
[TD]Ordered QTY[/TD]
[TD]MILLET[/TD]
[TD]WHEAT[/TD]
[TD]STARCH[/TD]
[TD]DEXTROSE[/TD]
[TD]GELATIN [/TD]
[TD]CARROT[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4200001[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]47940[/TD]
[TD="align: right"]46060[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4200002[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]13200[/TD]
[TD="align: right"]11000[/TD]
[TD="align: right"]8800[/TD]
[TD="align: right"]6600[/TD]
[TD="align: right"]4400[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4200001[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51000[/TD]
[TD="align: right"]49000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4200003[/TD]
[TD="align: right"]35[/TD]
[TD] [/TD]
[TD="align: right"]8750[/TD]
[TD="align: right"]52500[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]8750[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the board.

I'm not quite sure how to get that from a Pivot Table. I'll experiment when I get a chance. In the meantime, here's a way to do it with formulas. Assuming your ingredient table is on Sheet1, columns A:C, then these formulas should work:

	A	B	C	D	E	F	G	H	I	J
sequence	Line Code	Ordered QTY (ton)	MILLET	WHEAT	STARCH	DEXTROSE	GELATIN	CARROT

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4200001[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"]47940[/TD]
[TD="align: right"]46060[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4200002[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13200[/TD]
[TD="align: right"]11000[/TD]
[TD="align: right"]8800[/TD]
[TD="align: right"]6600[/TD]
[TD="align: right"]4400[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4200001[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]51000[/TD]
[TD="align: right"]49000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4200003[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8750[/TD]
[TD="align: right"]52500[/TD]

[TD="align: right"]8750[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=IFERROR($C2/(1/SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$B2,Sheet1!$B:$B,E$1)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E1[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$B$3:$B$20<>"",IF(MATCH(Sheet1!$B$3:$B$20,Sheet1!$B$3:$B$20,0)=ROW(Sheet1!$B$3:$B$20)-ROW(Sheet1!$B$3)+1,ROW(Sheet1!$B$3:$B$20))),COLUMNS($E$1:E$1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



These go on Sheet2. Columns A:C are your input data. Put the E1 formula in, change the references to match your sheet, then press Control+Shift+Enter. Then drag right as far as needed. Note that this is just to create a unique list of ingredients. If you have such a list, you can manually enter it and save some processing.

Now put the E2 formula in, and drag down and to the right as far as needed.

Hope this helps.