Inventory Macro Help

[sheed3k]

Ill try to explain my problem as best I can. I run a swimming pool
service company with 4 trucks that need to have the same inventory
loaded at the beggining of the season. I have an excel file with part
# and quanity that I require. I also have another file with my
complete inventory. I stock a lot more parts then I want in each
truck. What I would like to do is create a list of how much inventory
I have in stock of ONLY the items needed to stock the trucks. It cuts
my list in half and helps it easier to order what I need. I thought
maybe I could combine the truck list and the inventory list and delete
non duplicates from the inventory list. If this makes sense any help
would be appreciated. Thanks
Scott

 

[JLatham]

You might also consider adding another column to your main inventory list and
put an indicator in that column for the items that are stocked on the trucks
- maybe a simple "T". Then you could use Data | Filter | Autofilter to
quickly show just the items in your main inventory list that are in that
group.

If you still want to do something with combining things into one book and
removing non-duplicate entries, that can be done fairly easily. Others may
post some ideas before I come back and see if Data Filtering seems an
acceptable solution to you or not.

 

[sheed3k]

The problem with using an indicator is that I use a suite style
program for my company that includes inventory, payroll, customers
etc. When I do the truck inventory I export the warehouse inventory
and then compare it to what I want on the truck. I never use excel to
track inventory otherwise. So I would have to do this everytime I
exported creating the same amount of work.

 

[JLatham]

Let me see if I have a clear picture of this or if I've misinterpreted
something:

1) You have an Excel workbook with your truck's inventory items listed and
the quantities of the items required at the start of the year in that
workbook.

2) You have a non-Excel file source with your complete inventory list and
current quantities.

3) You have some way of exporting from the non-Excel file and then importing
the full inventory list into Excel.

4) Once the full list is imported into the Excel workbook with the truck
inventory list, you want to throw out any non-truck needed items from the
imported list so that you can see stock on hand for truck needed items easily.

Is that correct?

You mentioned a part # earlier, can we presume that part #s are truly unique
among the parts, and that it is the part # that would be used to identify
items used/not used on the trucks?

I presume you may have to do this relatively frequently, refreshing the main
inventory list in the Excel workbook from time to time and making comparisons
to ensure adequate quantities to restock the trucks as needed?

If I'm good so far, then need a little more information and we can probably
come up with something:

Column that the Part # is in on the existing Excel worksheet with truck
inventory. This worksheet's name also.
Column that the Part # would be in on the sheet with the imported complete
inventory listing. If you know sheet name that this list would be imported
to, that would be good also?
Version of Excel you are using.

 

[sheed3k]

1. Yes I have a list with Part # and quanity required for truck.
Column A is part # and column B is Quanity.

2. Yes I export my inventory with part # and quanity , into a text
file using a ";" to delimit the fields.

3. Yes, I import the inventory into Excel and set it up the same way
as the truck inventory. Using Column A as part # amd column B as
quanity.

4. Yes , I want to eliminate any part # and quanity that are not in
the truck inventory. If it is possible If i have something I want in
the truck that I dont have in inventory I would not like this deleted
from the truck inventory but could live with it if neccessary.

The part #s are unique as no part #s repeat. They use the same part #
if they are in the truck, warehouse etc.

I call the truck inventory file, "truckinv.xls" and full inventory
"compinv.xls"
Excel version 2000 9.0.3821 SR-1

Thanks Again

 

[JLatham]

Take a look at this Excel file:
http://www.jlathamsite.com/uploads/poolinventory.xls
It does contain macro code, so you will probably get a warning if your Macro
Security level is set to Medium (no warning if set to low [unsafe level!])
and it won't work at all if your Macro Security is set to High/Very High.

If needed, before opening the workbook, open Excel and set Macro Security
level to Medium:
Tools | Macro | Security (may have to expand list to see Security entry)
Choose Medium level. Close Excel. Reopen Excel and then the workbook.
Excel has to be closed/reopened after a macro security level change to make
the changes active.

The way I've set this up, right now you'd manually copy your entire
inventory list from the sheet in the compinv.xls file into the CompInv sheet
in this book. Things are automatic after that.

In using the VLOOKUP() formula the way I did on the TruckInv sheet, you
could add things to the truck list that aren't in the 'master' inventory list
and it would not mess up anything at all.

Link to the file:
http://www.jlathamsite.com/uploads/poolinventory.xls

To view the code that does the work, choose the TruckInv sheet, right-click
on the sheet's name tab and choose [View Code].

This uses a named range on the CompInv sheet that is dynamically updated any
time you make changes to it when you return to the TruckInv sheet. So the
TruckInv sheet results always reflect the current 'real-time' status based on
your company inventory list in the workbook.

This also does away with the need to actually delete entries from the
imported company inventory list - that's kept on a separate sheet, what
happens is that the quantity from that list is pulled into the same row with
the truck inventory items, so you only have to look at a single row to see
how many needed on truck, how many in inventory, plus I added a formula to
check and see when company stock falls below the level needed to fill a
single truck from empty state.

Hopefully this is a start...

 

[sheed3k]

WOW, I cant even belive how helpful you have been. Really it is
unbelivable, thank you so much. I am gonna play around with it but it
looks to do exactly what I wanted. Amazing thanks again.
Scott