How to handle inventory reports with excel over time?

geoi310

New Member
Joined
Jul 17, 2004
Messages
7
This was a question I asked a while back and got some great advice.

Background:
I run a few online retail stores that ship products from various 3rd party warehouses. These outside warehouses send me weekly quicbooks inventory reports output as excel files.

My goal was to track changes over time so I could spot items about to sell out and items that were back in stock.

Mr. Excel suggested I do this in Access and I have been doing just that for the last few months. I get the external data via excel spreadsheet, create a report using the wizard and add weekly data for abou 2 months worth of data. I then conditionally color format the fields to show items running low, or items back in stock. Works pretty good except for the fact that I need to manually import the data and re-create a report every week.

Question -
There are only 3 fields in the inventory report that I am intersted in when it gets sent to me as an excel file.
Item #, desctiption, available units.
The only field that changes over time are the available units.

Isn't there an easier way of compiling and analyzing this data with just excel?

Speficially - let's say I have this weeks data in 3 columns. I get next weeks data. What I want to do is to add a new column with the date, but make sure that the available units match the item#. Is there a way to import this column and make sure the data coincides with the item #?
e.g.
Item # | description | available units | available units week 2 etc. etc.
03454 | green lure | 300 | 250


Goal is to add a new column of inventory data every week quickly.

Any suggestions would be appreciated.

Thank you
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mezr

Active Member
Joined
Feb 2, 2003
Messages
301
Hi geoi310:

If I understand you right, you want a sheet that has the first two columns to be "fixed" with your "Item #" and "Item Description", and these two columns should list ALL of your items. Then you want there to be a new column each week to the right of these that lists the available inventory for each item, and the problem that you're having is that the reports being sent don't necessarily have ALL of your items listed, so you're struggling with getting their reports to match with your list ... is my interpretation correct?

If it is, then you could import their report into a new tab in your workbook, and then run a "VLOOKUP" formula in the first available column to the right of your list. This formula can match the item number on their list with yours, and then return the available inventory for that item.

I hope this helps.
 

geoi310

New Member
Joined
Jul 17, 2004
Messages
7
vlookup

Thank you for the tip. I'm looking into the vlookup functionality. It looks like it may do the trick once I get the exact syntax down.

I'm thinking the workflow will look like this -
I have a 2 column sheet with a unique product id #, and then the inventory quantity.
I get a new update sheet that I import to a second sheet.
I use vlookup to search for the same product line in the second sheet, then copy the iventory value in to the first sheet next to the old value.
This way, I would only get inventory updates for items that exist and it verifies that the inventory # always concides with the product #. So there's no chance of mixing up any #'s.
I would then have the main sheet which would show inventory#'s over time.

That's the theory, I'll let you know if I get the function to work in my real world project.

Thank you for the tip.
 

Watch MrExcel Video

Forum statistics

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