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.

sequence	Line Code	Ordered QTY (ton)
1	4200001	47
2	4200002	22
3	4200001	50
4	4200003	35

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Ingredient table

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. sequence Line Code Ordered QTY MILLET WHEAT STARCH DEXTROSE GELATIN CARROT 1 4200001 47 47940 46060 2 4200002 22 13200 11000 8800 6600 4400 3 4200001 50 51000 49000 4 4200003 35 8750 52500 8750 <colgroup><col><col><col><col span="3"><col><col span="2"></colgroup><tbody> </tbody> <colgroup><col><col><col><col span="3"><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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
1	sequence	Line Code	Ordered QTY (ton)		MILLET	WHEAT	STARCH	DEXTROSE	GELATIN	CARROT
2	1	4200001	47		47940	46060
3	2	4200002	22		13200	11000	8800	6600	4400
4	3	4200001	50		51000	49000
5	4	4200003	35			8750	52500			8750
6

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E2 =IFERROR($C2/(1/SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$B2,Sheet1!$B:$B,E$1)),"") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E1 {=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))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.