Finding Oldest value to make up specified sum

[sriche01]

Hello All,

I'm working on a first in first out orders vs inventory spreadsheet.

	A	B	C	D	E	F	G
1	Crop	Item	Qty		Item	Order Date	Qty
2	1	A	10		A	1/1/2019	50
3	4	A	20		A	1/2/2019	30
4	6	A	30		A	1/3/2019	90
5	8	A	40		A	1/4/2019	70
6	10	A	50		A	1/5/2019	40
7	13	A	60		A	1/6/2019	100
8	12	A	70		A	1/7/2019	80
9	15	A	80		A	1/8/2019	10
10	11	A	90		A	1/9/2019	20
11	14	A	100		A	1/10/2019	60
12	2	B	5		B	1/11/2019	32
13	5	B	8		B	1/12/2019	25
14	7	B	25		B	1/13/2019	16
15	9	B	32		B	1/14/2019	8
16	3	C	16		C	1/15/2019	5

<tbody>
</tbody>

Sheet1​

So hopefully this simplified sample data will work.
Inventory Data
Columns A,B,C Represent inventory data with a crop serial number (column A), Item Number (column B) and crop quantity (Column C).
Crop serial numbers (column A) are unique and are date based so that lower number means earlier date = older crop.

Order Data
Columns E, F G are orders we have against the inventory in the first three columns.
Again, we have Item number (column E), Ship date (column F), Quantity ordered (column G)

My goal is to identify the oldest crop number that will have a quantity available for a given order if we fill these orders using oldest crops to fill earliest orders for a given item.

So for the order for Item A going out on 1/6/19, my desired formula would return "12" because all crops of Item A Older than (lower crop number than) 12 are needed for earlier shipping orders. (Adding all orders before 1/6 totals 280. Crops before 12 for column A total 240)

The orders and the Inventory are actually on separate sheets of the same workbook and are different lengths.
Neither the orders nor the crops will always be sorted by date. There are some dates where multiple orders of the same item will ship. At this point I am just looking for the oldest crop that will have any inventory left at a given order date before anything ships on that date.

Somehow I feel like I've done something like this before, but can't quite put my finger on it...

Thanks in advance!

 

[sriche01]

Ok, Let's see if I can simplify this...

	E	F	G	H	I
1	Item	Order Date	Qty	Qty available	Oldest Crop Number
2	A	1/1/2019	50	550
3	A	1/2/2019	30	460

<tbody>
</tbody>

​

	A	B	C
1	Crop	Item	Qty
2	1	A	10
3	4	A	20
4	6	A	30
5	8	A	40
6	10	A	50
7	13	A	60
8	12	A	70
9	15	A	80
10	11	A	90
11	14	A	100
12	2	B	5
13	5	B	8
14	7	B	25
15	9	B	32
16	3	C	16

<tbody>
</tbody>

Sheet2​

What I really want is to identify the oldest crop available to make up a specified sum.

The crop numbers in Table 2, column A are exclusive and age related - the lower the number, the older the crop of a particular Item. So to start the year, we have a total of 550 "A" Items on inventory. That appears in column H of table 1. All 550 are available to pull for this first order. The second order of "A" items only has 460 to choose from because the first order is presumably gone.

What I want is to identify in column I the oldest (smallest) crop number that will still have inventory left in it by the time we get to the ship date for any given order. Now, we may have multiple orders of the same item going out on the same date, but we'll deal with that later. I simply want to match the oldest available crop with inventory in it to an order date.

Any ideas?

Thanks!

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H	I
1	Crop	Item	Qty		Item	Order Date	Qty	Earliest Batch with available qty
2	1	A	10		A	1/1/2019	50	1
3	4	A	20		A	1/2/2019	30	6
4	6	A	30		A	1/3/2019	90	8
5	8	A	40		A	1/4/2019	70	11
6	10	A	50		A	1/5/2019	40	12
7	13	A	60		A	1/6/2019	100	12
8	12	A	70		A	1/7/2019	80	14
9	15	A	80		A	1/8/2019	10	14
10	11	A	90		A	1/9/2019	20	15
11	14	A	100		A	1/10/2019	60	15
12	2	B	5		B	1/11/2019	32	2
13	5	B	8		B	1/12/2019	25	7
14	7	B	25		B	1/13/2019	16	9
15	9	B	32		B	1/14/2019	8	Not available
16	3	C	16		C	1/15/2019	5	3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula H2 {=IFERROR(SMALL(IF(SUMIFS($C:$C,$B:$B,E2,$A:$A,"<="&ROW(INDIRECT("1:20")))>SUMIFS($G:$G,$E:$E,E2,$F:$F,"<"&F2),ROW(INDIRECT("1:20"))),1),"Not available")} <thead> </thead><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>

The INDIRECT("1:20") represents the upper number of crops you have.

 

[sriche01]

The INDIRECT("1:20") represents the upper number of crops you have.

Thanks, Eric, for helping me with this. So if I am understanding right, the 1:20 just assumes I have 20 or fewer crop lines for a given item, or does it mean the serial numbers themselves are lower than 20?

My actual crop numbers are 6 digits long. I just used small numbers for simplicity. But I would be well within the 20 crops available parameter...

 

[Eric W]

If the lowest serial number in your list is 123456, and the highest is 234567, then use INDIRECT("123456:234567"). You'd want to use the smallest range you can, since the more numbers the formula has to check, the slower the formula will be.

 

[sriche01]

If the lowest serial number in your list is 123456, and the highest is 234567, then use INDIRECT("123456:234567"). You'd want to use the smallest range you can, since the more numbers the formula has to check, the slower the formula will be.

Thanks, just got it working using a minifs and maxifs to set the range. Works Great! Thank you very much!

 

[Eric W]

You're very welcome!