Update item list with dates

[leebauman]

Hello, I have a list of items currently in my inventory and a separate list indicating when I will receive shipments (open purchase orders) of additional items.

I want to extrapolate the date and quantity data from the open purchase orders sheet to understand how inventory levels will adjust going forward. Crude example below.

Book2
	A	B	C	D	E	F	G	H	I
1	Item	Current Qty	1/15/2021	2/15/2021	2/20/2021	3/7/2021	2/5/2021	4/1/2021	5/23/2021
2	Apple	-10	4	4	10
3	Pear	-2				4
4	Orange	0					19
5	Banana	-25						18	20
6
7	Item	Qty	Date
8	Grape	3	1/1/2021
9	Apple	4	1/15/2021
10	Mango	1	2/3/2021
11	Orange	19	2/5/2021
12	Avocado	2	3/6/2021
13	Pear	4	3/7/2021
14	Banana	18	4/1/2021
15	Apple	4	2/15/2021
16	Apple	10	2/20/2021
17	Banana	20	5/23/2021
Sheet1

 

[Peter_SSs]

Would this suffice?

21 02 08.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Current Qty	1/01/2021	15/01/2021	3/02/2021	5/02/2021	15/02/2021	20/02/2021	6/03/2021	7/03/2021	1/04/2021	23/05/2021
2	Apple	-10		4			4	10
3	Pear	-2								4
4	Orange	0				19
5	Banana	-25									18	20
6
7	Item	Qty	Date
8	Grape	3	1/01/2021
9	Apple	4	15/01/2021
10	Mango	1	3/02/2021
11	Orange	19	5/02/2021
12	Avocado	2	6/03/2021
13	Pear	4	7/03/2021
14	Banana	18	1/04/2021
15	Apple	4	15/02/2021
16	Apple	10	20/02/2021
17	Banana	20	23/05/2021
Inventory

Cell Formulas
Range	Formula
C1:L1	C1	=TRANSPOSE(SORT(UNIQUE(C8:C17)))
C2:L5	C2	=FILTER($B$8:$B$17,($A$8:$A$17=$A2)*($C$8:$C$17=C$1),"")
Dynamic array formulas.

 

[Peter_SSs]

.. or if you don't want those vacant columns.

21 02 08.xlsm
	A	B	C	D	E	F	G	H	I
1	Item	Current Qty	15/01/2021	5/02/2021	15/02/2021	20/02/2021	7/03/2021	1/04/2021	23/05/2021
2	Apple	-10	4		4	10
3	Pear	-2					4
4	Orange	0		19
5	Banana	-25						18	20
6
7	Item	Qty	Date
8	Grape	3	1/01/2021
9	Apple	4	15/01/2021
10	Mango	1	3/02/2021
11	Orange	19	5/02/2021
12	Avocado	2	6/03/2021
13	Pear	4	7/03/2021
14	Banana	18	1/04/2021
15	Apple	4	15/02/2021
16	Apple	10	20/02/2021
17	Banana	20	23/05/2021
Inventory (2)

Cell Formulas
Range	Formula
C1:I1	C1	=TRANSPOSE(SORT(UNIQUE(FILTER(C8:C17,ISNUMBER(MATCH(A8:A17,A2:A5,0))))))
C2:I5	C2	=FILTER($B$8:$B$17,($A$8:$A$17=$A2)*($C$8:$C$17=C$1),"")
Dynamic array formulas.

 

[leebauman]

Yeah, that works perfectly. Thanks so much for the quick help!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[leebauman]

After playing around, I'm realizing it's a bit more complex.

The initial list is an order list by item. The quantities are on backorder and I need to extrapolate date and quantity info from an open purchase order list. Updated scenario below.

Thanks again.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Order #	Date	Item	Qty on Order	1/15/2021	2/15/2021	2/20/2021	4/1/2021	2/5/2021	4/1/2021	5/23/2021	3/7/2021	2/20/2021	2/5/2021	2/20/2021	5/23/2021
2	111	1/1/2021	Apple	-10	4	4	2
3	222	1/15/2021	Banana	-3				3
4	333	1/23/2021	Orange	-9					9
5	444	1/25/2021	Banana	-25						15	10
6	555	2/2/2021	Pear	-2								2
7	666	2/4/2021	Apple	-6									6
8	777	3/1/2021	Orange	-3										3
9	888	3/5/2021	Apple	-1											1
10	999	4/1/2021	Banana	-10												10
11
12
13	Item	Qty	Date
14	Grape	3	1/1/2021
15	Apple	4	1/15/2021
16	Mango	1	2/3/2021
17	Orange	19	2/5/2021
18	Avocado	2	3/6/2021
19	Pear	4	3/7/2021
20	Banana	18	4/1/2021
21	Apple	4	2/15/2021
22	Apple	10	2/20/2021
23	Banana	20	5/23/2021
Sheet1

 

[Peter_SSs]

Like this?
I have used a custom number format in the formula cells of 0;0;"" to hide zero values. No need to do this if you are happy to see zeros in the empty cells.
I don't see why you have 15 and 10 for Banana - hopefully that was a typo.

21 02 08.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Order #	Date	Item	Qty on Order	15/01/2021	5/02/2021	15/02/2021	20/02/2021	7/03/2021	1/04/2021	23/05/2021
2	111	1/01/2021	Apple	-10	4		4	2
3	222	15/01/2021	Banana	-3						3
4	333	23/01/2021	Orange	-9		9
5	444	25/01/2021	Banana	-25						18	7
6	555	2/02/2021	Pear	-2					2
7	666	4/02/2021	Apple	-6	4		2
8	777	1/03/2021	Orange	-3		3
9	888	5/03/2021	Apple	-1	1
10	999	1/04/2021	Banana	-10						10
11
12
13	Item	Qty	Date
14	Grape	3	1/01/2021
15	Apple	4	15/01/2021
16	Mango	1	3/02/2021
17	Orange	19	5/02/2021
18	Avocado	2	6/03/2021
19	Pear	4	7/03/2021
20	Banana	18	1/04/2021
21	Apple	4	15/02/2021
22	Apple	10	20/02/2021
23	Banana	20	23/05/2021
Inventory (3)

Cell Formulas
Range	Formula
E1:K1	E1	=TRANSPOSE(SORT(UNIQUE(FILTER(C14:C23,ISNUMBER(MATCH(A14:A23,C2:C10,0))))))
E2:K10	E2	=MIN(-$D2-SUMIF($D2:D2,">0"),FILTER($B$14:$B$23,($A$14:$A$23=$C2)*($C$14:$C$23=E$1),0))
Dynamic array formulas.

 

[leebauman]

Yes, my mistake on the banana counts. Many thanks for the solution.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.