Identify the earliest expiry date on stock based on current stock levels

**julie86xx** · 10-09-2013, 09:47 PM

Hi I don't use excel often but have been asked to identify the earliest expiry date based on an excel spreadsheet that is used for stock management so looking for some assistance here.

I have a list of records of stock orders received which has the type of stock, number of stock and the expiry date of the stock - example image attached below.
order summary.gif

When purchases of the stock are made, the earliest stock received will be sold.
Based on the number of stock remaining on hand (which I can obtain from storage), I would like to see what the earliest expiry date would be and the quantity of stock that the date is applicable to.

For example:
I have received a total of 50 for the stock item '590002' where 20 will expire on 30/06/2014, 20 will expire on 16/08/2014 and 10 will expire on 7/07/2015.
If 27 of the 50 have been purchased and I enter this value somewhere on the spreadsheet, I'd like to be able to see what the earliest expiry date is on the remaining 23 stock and
As the stock expiring on the 30/06/2014 has been all sold, then I would expect to see that it has been able to calculate that the earliest expiry date is 16/08/2014 and is applicable to 13 stock.

Hope this makes sense and many thanks in advance!

**QuantumPolagnus** · 10-09-2013, 10:57 PM

You could always use the Sort function in Excel to sort the table by the Expiry Date column. That would make it easy to see. If you need it to be displayed in another fashion, you could always resort it by Order Number if that's how you want it displayed.

This can be done by clicking on any cell within the table, opening the Data tab and selecting Sort (it should automatically select the entire table once you click "Sort"). It should give you some options of how you want to sort (By what columns, by increasing or decreasing order, etc.) and you can refine the sorting process by adding more levels of sorting (i.e.: you could sort by Expiry Date and then by Number of Stock, etc. etc.).

I'm not sure about Excel 2010, but I'm pretty sure there should be a "my data has headers" checkbox you can click to make sure it doesn't try to sort the column labels as part of the data. Maybe someone else can confirm that.

You could use equations to give you the relevant data in a separate cell(s), but Sorting is probably the easiest way to get that info.

**julie86xx** · 10-10-2013, 01:08 AM

Thanks Quantum Polagnus, I'm not sure if I haven't explained the scenario and requirements properly, however sorting isn't going to give me the results I need unfortunately.
The actual table consists of a huge range of stock types, and will have multiple stock orders associated to it, so I'm not going to be able to locate the upcoming expiry date for each stock type by just sorting.