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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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