FIFO Excel - Mark Items as Sold - doable in Excel?

[Markesh]

I am creating an excel inventory and am running into a challenge that has stumped the Excel "masters" I know.


I have one worksheet that has the following column structure. Practically this list represents every single physical inventory item in a store, it's wholesale cost, and the date the item was received. Duplicates are allowed:


SKU COST DATE_RECEIVED


And then, I have a second worksheet that has the following column structure. Practically, this list represents all individual items that were sold at the store. Duplicates are allowed:


SKU SOLD_PRICE DATE_SOLD


The problem is I'm trying to introduce the DATE_SOLD and the SOLD_PRICE columns into the first worksheet. With no dupes, this is easy. But when we have dupes in each table, it has stumped us.


I ultimately want a FIFO method where when an item sells and thus shows up in the second table, it finds the first (by DATE_RECEIVED), "unsold" item in the first table with a matching SKU, and insert the DATE_SOLD and SOLD_PRICE columns of data into that item in table one.


Any ideas???

 

[Gerald Higgins]

Does every individual item in stock have its own record ?

So for example, if you have 5 items in stock of SKU #1, does that mean 5 separate records in the worksheet ?
Or is it 1 record, showing 5 units in stock ?

 

[Markesh]

Hello Gerald, Thanks for your reply. Yes, in your example, SKU #1 would have 5 records in the worksheet. So essentially, we would like to mark each record as sold, along with the selling details, to each record as it happens.

 

[Matty]

Hi,

Could you provide a small example to illustrate what you're wanting? This will ensure there is no misinterpretation of the request.

Matty

 

[Markesh]

Hi All, Here is a sample file that shows what I am trying to accomplish.

The first worksheet, entitled "Inventory" comes from our inventory system. The second worksheet, entitled "Sales" comes from a Point of Sale system.

The "Results" worksheet is the outcome I am looking for, whereby each record in Sales gets paired with a single record from Inventory. All unpaired Inventory items remain on the list, so we can then see what hasn't sold as well.

The pairing method for "selling" inventory should leverage the FIFO mindset where that is determined by the DATE_RECEIVED column in Inventory worksheet.

Thanks to you both for your time in helping me figure this out!

Here is a link to the sample file: https://www.dropbox.com/sh/dvtf8h3obr4nqjo/AAByeqXzGu-sYP9LQ3kSHs7Fa?dl=0

 

[Matty]

Hi,

Assuming the data in cells A1:C9 in the 'RESULTS' Worksheet already exists, try the following in D2:

=IFERROR(INDEX(SALES!B$2:B$5,SMALL(IF(SALES!$A$2:$A$5=$A2,ROW(SALES!B$2:B$5)-ROW(SALES!B$2)+1),COUNTIF($A$2:$A2,$A2))),"No Sales Data")

The formula needs committing with CTRL+SHIFT+ENTER and can then be copied down and across. These cells can then be formatted appropriate to the data being displayed.

Hope this helps.

Matty

 

[Markesh]

Matty - Thank you, that works great!

One question - is there a way to modify the formula so it also highlights any items in the Sales sheet that are not successfully matched up with the inventory tab? This would help me identify errors that were entered into the POS system.

Thanks!