Referencing a list of order QTYs against parts by week number

[anton123]

Hi,

I have been trying to reference a list of orders into a time line plan for a product.

The report data is listed by part number and re-occurs based on different Purchase Orders (POs) - image Order List — imgbb.com

My time line plan is organised by part number - image Time Line — imgbb.com

What I need to do is get all of the orders from the report data organised into the time line plan based on Part then week, returning the values in columns F-AA on the attached images. I have included statements in row 3 to explain fully the data I am looking to populate each column with.

I have tried to use vlookups which works but this failed as it doesn't account for multiple entries of the same value within the data. I have tried combining IF statements with vlookups and again only limited success.

Looking around online I believe it may be Index and Match functions I need, but I have limited knowledge of how to make this work. can anyone advise?

If anyone has a solution to the data I need in columns C, D and AC I would also appreciate it.

Thank you,

 

[oldbrewer]

item	supplier	qty	deliv date	weeknum
item1	s1	10	01/08/2017	31
item2	s3	15	09/08/2017	32
item2	s3	20	17/08/2017	33
item3	s2	25	25/08/2017	34						date	week
item4	s1	30	02/09/2017	36						29/07/2017	31
item5	s3	35	10/09/2017	37						05/08/2017	32
item1	s1	40	18/09/2017	38						12/08/2017	33
item1	s1	35	10/08/2017	32						19/08/2017	34
item2	s3	30	04/10/2017	40						26/08/2017	35
item3	s2	25	12/10/2017	41						02/09/2017	36
item3	s2	20	20/09/2017	38						09/09/2017	37
item4	s1	15	28/10/2017	44						16/09/2017	38
item1	s1	10	05/11/2017	45						23/09/2017	39
item5	s3	30	13/11/2017	46						30/09/2017	40
										07/10/2017	41
										14/10/2017	42
										21/10/2017	43
										28/10/2017	44
										04/11/2017	45
										11/11/2017	46
										18/11/2017	47
										25/11/2017	48
										02/12/2017	49
										09/12/2017	50
										16/12/2017	51
										23/12/2017	52
										30/12/2017	53
				week number
item	tot qty	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53
item1	95	10	35						40							10
item2	65		15	20							30
item3	70				25				20			25
item4	45						30								15
item5	65							35									30
			formula giving 10 for item1 week 31
	=IF(SUMPRODUCT(($A$2:$A$15=$A31)*($E$2:$E$15=C$30)*($C$2:$C$15))=0,"",SUMPRODUCT(($A$2:$A$15=$A31)*($E$2:$E$15=C$30)*($C$2:$C$15)))

<colgroup><col span="3"><col><col><col><col><col><col><col><col><col span="2"><col span="12"><col></colgroup><tbody>
</tbody>

 

[anton123]

Brilliant, thank you very much

 

[oldbrewer]

glad to help no doubt you can tweak it to your specific needs