Need help with look up formula

[chadwood3232]

Hello.

Table 1 "Raw Data" - This is raw data that is pulled giving me EAN/UPC #, the batch, next inspection date, expiration date and unit quantity.

Each EAN/UPC # will have multiple batches and different inspection and expiration dates.

Table 2 "Sls Plan" -Simply just the sales forecast by EAN/UPC by month.

Table 3 "

RawDataPT with Sales
" -A pivot table based on "Raw Data". Bringing in EAN/UPC, expiration date, and unit quantity. Then to the right I did lookup to bring in sales plan for that EAN/UPC #.

Tab 3 "Sls Plan" -Simply just the sales forecast by EAN/UPC by month.



So what I am trying to do is this on the "RawDataPT with Sales" I want to find a way to apply the sales forecast starting in column E row 6 for material 1(50 units). I would want to apply that to the date that expires first. Which is 01/31/205. So in this example for the item that expires on 01/31/2025 I currently have 158 units(cell C8) so for the Nov sales forecast units (50 units) I would want to apply 50 units to 01/31/2025. Then Dec take sales of 60 and apply to balance of the 01/31/2025. Then Jan apply 48 units to 01/31/2025 then use the balance 22 units to the next date that expires next which would be 05/31/2025.

Starting in row 35 i have mocked up a basic idea but not locked into that type of format. It doesn't need to be this format. It can look like anything i just need to apply sales forecast to each material (1 & 2) in this example until it gets to zero to ensure it is sold before it expires. Thank you to anyone that can help

Raw Data

onhandtest.xlsx
	A	B	C	D	E
1	EAN/UPC	Batch	Next Inspection Date	Expiration Date	Inventory Qty 16 OCT 23
2	1	1	03/31/2024	01/31/2025	98
3	1	2	03/31/2024	01/31/2025	20
4	1	3	03/31/2024	01/31/2025	40
5	1	5	07/31/2024	05/31/2025	80
6	1	6	12/31/2024	10/31/2025	100
7	1	7	01/31/2025	11/30/2025	20
8	1	8	12/31/2024	12/31/2025	40
9	1	4	07/31/2024	04/30/2026	60
10	2	1	03/31/2024	01/31/2025	98
11	2	2	03/31/2024	01/31/2025	20
12	2	3	03/31/2024	01/31/2025	40
13	2	5	07/31/2024	05/31/2025	80
14	2	6	12/31/2024	10/31/2025	100
15	2	7	01/31/2025	11/30/2025	20
16	2	8	12/31/2024	12/31/2025	40
17	2	4	07/31/2024	04/30/2026	60
Invlevel

Sales Plan Data

onhandtest.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Date (Year)	Date (Month)
2		2023												2024
3	Selling Material	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
4	1	50	60	70	80	90	100	110	120	130	140	150	160	170	180	190
5	2	40	60	80	100	120	140	160	180	200	220	240	260	280	300	320
6
7
Sls Plan

RawDataPT with Sales

onhandtest.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L								T	U	V	W	X	Y	Z
3					Sales Forecast
4					2023		2024
5				EAN/UPC	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
6				1	50	60	70	80	90	100	110	120
7	EAN/UPC	Expiration Date	Sum of Inventory Qty	2	40	60	80	100	120	140	160	180
8	1	01/31/2025	158
9	1	04/30/2026	60
10	1	05/31/2025	80
11	1	10/31/2025	100
12	1	11/30/2025	20
13	1	12/31/2025	40
14	2	01/31/2025	158
15	2	04/30/2026	60
16	2	05/31/2025	80
17	2	10/31/2025	100
18	2	11/30/2025	20
19	2	12/31/2025	40
20
21
22
23	Current idea				Sales Forecast
24					2023		2024
25				EAN/UPC	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun									Unit Balance
26				1	50	60	70	80	90	100	110	120									2023		2024
27				2	40	60	80	100	120	140	160	180									Nov	Dec	Jan	Feb	Mar	Apr
28	EAN/UPC	Expiration Date	Sum of Inventory Qty
29	1	01/31/2025	158		50	60	48														108	48	0	0	0	0
30	1	04/30/2026	60							52	8										60	60	60	60	60	8
31	1	05/31/2025	80				22	58													80	80	58	0	0	0
32	1	10/31/2025	100					22	78												100	100	100	78	0	0
33	1	11/30/2025	20						12	8											20	20	20	20	8	0
34	1	12/31/2025	40							40											40	40	40	40	40	0
35	2	01/31/2025	158		40	60	58														118	58	0	0	0	0
36	2	04/30/2026	60						2	58											60	60	60	60	58	0
37	2	05/31/2025	80				22	58													80	80	58	0	0	0
38	2	10/31/2025	100					42	58												100	100	100	58	0	0
39	2	11/30/2025	20						20												20	20	20	20	0	0
40	2	12/31/2025	40						40												40	40	40	40	0	0
41
42
43
invlevelPT

Cell Formulas
Range	Formula
E6:L6	E6	=VLOOKUP($A8,'Sls Plan'!$A:$AE,E1,0)
E7:L7	E7	=VLOOKUP($D7,'Sls Plan'!$A$4:$Q$5,E1,0)
E26:L27	E26	=E6
U29:U40	U29	=$C29-E29
V29:Z40	V29	=U29-F29

 

[GuzmanServices]

Thank you for the inquiry. Unfortunately, I was forced to make slight changes to the presentation in the "Current idea" section, as shown below the following formulas for Cells F29 & F36.
These were previously on Column E, but a helper column was needed - as well as a blank Row 35. Note the color for the headers on Row 28 were changed, to indicate it's not a Pivot Table.

Formula for Cell F29, to be Copied and Pasted on Cells F29 to M34:​

=IF($D29=1,MIN(SUMIFS($C$29:$C$41,$A$29:$A$41,$A29,$B$29:$B$41,$B29),F$26,$C29-SUM($E29:E29)),MIN($C29-SUM($E29:E29),F$26-SUM(F$28:F28)))

​

Formula for Cell F36, to be Copied and Pasted on Cells F36 to M41:​

=IF($D36=1,MIN(SUMIFS($C$29:$C$41,$A$29:$A$41,$A36,$B$29:$B$41,$B36),F$27,$C36-SUM($E36:E36)),MIN($C36-SUM($E36:E36),F$27-SUM(F34:F$35)))

 

[chadwood3232]

Hello. Thank you so much for your response. I am sorry for just now getting back to you. But this is very helpful thank you. Can I ask another question? I have multiple products(over 200). Is there a formula to create the rank in column D? I should be able to do that but drawing a blank. Thank you once again.

 

[GuzmanServices]

The first set of formulas above in Column D were written as follows, =RANK($B29,$B$29:$B$34,1)
This should work with your 200+ product range, as long as the data is written in contiguous vertical cells. Good luck!

 

[chadwood3232]

The first set of formulas above in Column D were written as follows, =RANK($B29,$B$29:$B$34,1)
This should work with your 200+ product range, as long as the data is written in contiguous vertical cells. Good luck!

hello. thank you so much for the response. Is there a way to do the rank every time column A changes? It is not a static list and will change. So this formula would take some time to copy down each time i do that update. Thank you once again for your time.

 

[GuzmanServices]

You're welcome. All that you need to do is change the Row numbers, i.e. $29 or $34, as needed.