Finding Oldest value to make up specified sum

S

sriche01

Board Regular
Joined
Dec 24, 2013
Messages
136
Office Version
  1. 365
Platform
  1. Windows
Hello All,

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

ABCDEFG

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Crop[/TD]
[TD="align: center"]Item[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Item[/TD]
[TD="align: center"]Order Date[/TD]
[TD="align: center"]Qty[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]50[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/2/2019[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/3/2019[/TD]
[TD="align: center"]90[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/4/2019[/TD]
[TD="align: center"]70[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/5/2019[/TD]
[TD="align: center"]40[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/6/2019[/TD]
[TD="align: center"]100[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]70[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/7/2019[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]80[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/8/2019[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]90[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/9/2019[/TD]
[TD="align: center"]20[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/10/2019[/TD]
[TD="align: center"]60[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]1/11/2019[/TD]
[TD="align: center"]32[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]1/12/2019[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]1/13/2019[/TD]
[TD="align: center"]16[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]1/14/2019[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]1/15/2019[/TD]
[TD="align: center"]5[/TD]

</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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Ok, Let's see if I can simplify this...

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item[/TD]
[TD="align: center"]Order Date[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Qty available[/TD]
[TD="align: center"]Oldest Crop Number[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]50[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1/2/2019[/TD]
[TD="align: center"]30[/TD]
[TD="align: right"]460[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

​

ABC

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Crop[/TD]
[TD="align: center"]Item[/TD]
[TD="align: center"]Qty[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]20[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]40[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]50[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]60[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]70[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]90[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]100[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]32[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]16[/TD]

</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!
 
Upvote 0
Maybe:

ABCDEFGHI
CropItemQtyItemOrder DateQtyEarliest Batch with available qty
AA
AA
AA
AA
AA
AA
AA
AA
AA
AA
BB
BB
BB
BBNot available
CC

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/2/2019[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/3/2019[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/6/2019[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/7/2019[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/8/2019[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"]90[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/9/2019[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/10/2019[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/11/2019[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/12/2019[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/13/2019[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]32[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/14/2019[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=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")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The INDIRECT("1:20") represents the upper number of crops you have.
 
Upvote 0
The INDIRECT("1:20") represents the upper number of crops you have.
Click to expand...

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...
 
Upvote 0
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.
 
Upvote 0
Eric W said:
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.
Click to expand...

Thanks, just got it working using a minifs and maxifs to set the range. Works Great! Thank you very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Pulling Correct Number of Dates Based on Column Value
Replies
1
Views
41
dreid1011
dreid1011
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
186
kvsrinivasamurthy
K
E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
PuntingJawa
  • Solved
If column B has # then pull data from column A beside it
Replies
4
Views
223
PuntingJawa
PuntingJawa
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top