Detrmine if inventory movement is occuring in FIFO order

Todd R K

Board Regular
Joined
Nov 3, 2004
Messages
90
Good afternoon!

Background

Each day, I receive an inventory report that I paste into a different tab labeled with the current date (the tabs are numbered according to the numerical equivalent of the date). The list will change in length and composition on a daily basis but will usually be approximately 1200 rows long.

Goal

I need to compare the current list with the previous day's list to determine if the inventory was utilized in FIFO order. In other words, the oldest inventory must be used first.

Examples

In the examples below, I have created a small sample of two consecutive inventory reports. When you look at the second report, you can see that the Gizmo inventory from container 0003 dated 05/05/05 was used before the Gizmo inventory dated 11/09/04, which is also in container 0003. This is determined by the change in the inventory amount from 690,000 to 400,000.

In addition, the Widget inventory from container 0017 dated 11/30/05 was used before the Widget inventory from container 0001 dated 11/18/05. Also, when a container is completely emptied as in the case of container 0017, it is removed from the list the following day.

Both of the above types of scenarios will usually occur each day.

Finally, there are 7 types and approximately 10 subtypes.

Summary

I would like to have a small matrix on a separate sheet with a column for each of the 7 types that would alert me if newer inventory were used before older inventory.

How can I achieve this?

Thank you

Todd
FIFO Tracking 112205.xls
ABCDE
1ContainerTypeSubtypeFIFO DateAmount
20001Widget301Nov 18 2005208,000
30001Widget301Nov 23 200536,000
40003Gizmo202Nov 9 2004540,000
50003Gizmo202May 5 2005690,000
60017Widget301Nov 30 2005480,000
38688
FIFO Tracking 112205.xls
ABCDE
1ContainerTypeSubtypeFIFO DateAmount
20001Widget301Nov 18 2005208,000
30001Widget301Nov 23 200536,000
40003Gizmo202Nov 9 2004540,000
50003Gizmo202May 5 2005400,000
38689
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Todd R K

Board Regular
Joined
Nov 3, 2004
Messages
90
Good morning:

After re-reading my post, I think it may have been too long. Here is the abridged version.

If the inventory amount changes, was it the oldest inventory? If not, then what is the date that should have been used?

I think I am struggling with how to perform multi conditional look-ups or I am approaching the problem incorrectly. For example, I would like to identify the container # with the oldest inventory of a particular type, subtype from the previous day and compare the inventory amount to the next day's inventory report for that container.

Does this clarify my problem or am I still not articulating the issue concisely?

Thank you

Todd
 

Todd R K

Board Regular
Joined
Nov 3, 2004
Messages
90
Friends

I think I have finally been able to articulate my questions based on the HTML example below:

=if(B3:B1200="widget",and,C3:C1200=301,what is the earliest FIFO date)
(Answer is 11/18/05)
=if(B3:B1200="widget",and,C3:C1200=301,what is the container that matches the FIFO date found above) (Answer is 0001)

With these two formulas, I can identify the container that should have been used. The final test would be:

=if(B3:B1200="widget",and,C3:C1200=301,what is the container number whose amount changed from yesterday's inventory report) (answer is 0003)

With the above information, I know that the wrong container was used and with conditional formating, I can create an "alert" in my Dashboard.

How do I construct the multi conditional formulas that I have described above?

Thank you

Todd
FIFO Tracking 112205.xls
ABCDE
1Yesterday's inventory report
2ContainerTypeSubtypeFIFO DateAmount
30001Widget301Nov 18 2005208,000
40001Widget301Nov 23 200536,000
50003Gizmo202Nov 9 2004540,000
60003Gizmo202May 5 2005690,000
70017Widget301Nov 30 2005480,000
8
9
10Today's inventory report
11ContainerTypeSubtypeFIFO DateAmount
120001Widget301Nov 18 2005208,000
130001Widget301Nov 23 200536,000
140003Gizmo202Nov 9 2004540,000
150003Gizmo202May 5 2005400,000
38688
 

Todd R K

Board Regular
Joined
Nov 3, 2004
Messages
90
Good afternoon:

The more I work on this issue, the more it seems that applying a logical test to a range versus a single cell is the answer (e.g. the if statement below that is looking at the range B3:B1200 or C3:C1200).

=if(B3:B1200="widget",and,C3:C1200=301,what is the earliest FIFO date)

I have performed searches but have not found anything that addresses this issue.

Any suggestions?

Thank you

Todd
 

Watch MrExcel Video

Forum statistics

Threads
1,118,056
Messages
5,569,951
Members
412,299
Latest member
agentless
Top