Merge inventory from one excel spreadsheet to existing sheet?

[internetlad]

Okay, I'm going to try to explain this as thoroughly as I can so there are no mix-ups about what i'm trying to do. I have two existing excel spreadsheets with company inventory on them. The first has the part number and the cost of all items ever put into inventory, the second is for inventory count, but does not include every historical item as we remove them as they obsolete.

Is there an automatic way to merge/combine the two sheets so that Excel can intelligently compare the part numbers and add the inventory amount from the second sheet? For example part 11101 may not exist on sheet two, but if part 11111 does then a column called "stock" should be applied to a similar column in sheet 1?

Thanks in advance for any and all input, I've been racking my brain trying to get this done without having to add hundreds of items in to the first sheet by hand.

 

[ex_bubblehead]

I know you won't want to hear this but you really should be using a database instead of a spreadsheet for this purpose (proper tool for the proper job) You can do what you ask but the code required will be very, very ugly, and prone to error.

 

[internetlad]

Yeah, honestly that's what I was expecting, and I'm not the one who set this up originally, just the guy whose lap it landed in after somebody who gets paid more than me got told there was no easy way out by an "excel expert" (read: friend of said guy).

Guess I should saddle up for a late night or two eh?

EDIT: would it help at all if we were to convert these to databases? AFAIK all the machines we're running have Access, so it's not a matter of not having the software, just a matter of somebody making them in excel in the first place.

 

[Calculagator]

Yeah, honestly that's what I was expecting, and I'm not the one who set this up originally, just the guy whose lap it landed in after somebody who gets paid more than me got told there was no easy way out by an "excel expert" (read: friend of said guy).

Guess I should saddle up for a late night or two eh?

EDIT: would it help at all if we were to convert these to databases? AFAIK all the machines we're running have Access, so it's not a matter of not having the software, just a matter of somebody making them in excel in the first place.

Excel sheets import pretty nicely into Access. There will be a learning curve if you haven't used it before, but it can do what you want without much hassle. It's a basic JOIN operation.

 

[USAFRet]

How familiar are you with Access? I could work up an easy conversion for you if you want.

 

[internetlad]

Unfamiliar but I pick up on stuff pretty quick. If it's a built in function I could research my way through it in probably an hour or so. If you're willing to lend a hand it'd be foolish to say no, though. I'd appreciate it thoroughly.

 

[USAFRet]

What version of Office are you using? 2007, 2010, 2013....

EDIT: in any case, this will be for 2013. But should be immediately usable in 2010, and probably 2007.

 

[USAFRet]

Writing this and making the pics took far longer than actually making it

Assumptions:
2 Excel files, Cost and Stock

Cost – 2 columns. Has every item ever
PartNum, Cost

Stock – 2 columns, has only current items
PartNum, Stock (Inventory)

(Notice only some of the part numbers show)

Create an Access db
Create a table in the Access, with these cols

LINK the two Excel workbooks. Access sees mostly as internal tables.

Create an Append query:
Here’s the SQL (you can copy paste this)
INSERT INTO Inventory ( PartNum, Cost, Stock )
SELECT PartnumCost.PartNum, PartnumCost.Cost, PartnumInventory.Stock
FROM PartnumCost LEFT JOIN PartnumInventory ON PartnumCost.PartNum = PartnumInventory.PartNum;

(the JOIN properties are key here)

This reads all the Excel rows, and Appends them to the Access table
ALL from the COST table, ALL from the STOCK table where PartNum meets.

Update Active
Create an Update query
This marks as Active anything with a STOCK amount. (For future use, Active Y/N allows for a STOCK amount of 0. "We still use this, we just don't have any right now")

Here’s the SQL
UPDATE Inventory SET Inventory.Active = 1
WHERE (((Inventory.Stock) Is Not Null));

This says that ‘Anything with a ‘Cost’ is active’.
All items live in the table permanently, just uncheck ‘Active’ when something is discontinued.

Here’s the result

 

[USAFRet]

Now everything lives in that Access, and you can kick the Excel files to the curb.

It is easy to outrun the DB capabilities of Excel
It is easy to get in over your head with Access.

 

[internetlad]

This is fantastic. I'll implement it tomorrow. Thanks for the help everyone, this is above and beyond the call of duty!

EDIT: and yeah it's 2013.

 

[USAFRet]

Good luck!

 

[Alabalcho]

This is fantastic. I'll implement it tomorrow. Thanks for the help everyone, this is above and beyond the call of duty!
EDIT: and yeah it's 2013.

Don't fordet that you will need Office Professional in order to use Access. It might turn out that getting off-the-shelf inventory application will be cheaper.

 

[USAFRet]

This is fantastic. I'll implement it tomorrow. Thanks for the help everyone, this is above and beyond the call of duty!
EDIT: and yeah it's 2013.

Don't fordet that you will need Office Professional in order to use Access. It might turn out that getting off-the-shelf inventory application will be cheaper.

He stated they already have Access.

 

[internetlad]

Well, it was a winding road complicated by mismatch errors and formatting issues, but I finally managed to get to the end goal with it. Thank you deeply for your assistance. Like you said it probably only took you a few minutes to bang out that code but it more than likely saved dozens of manhours cleaning up the data by hand. Thank you, USAFRet, and to everybody else who offered their time towards the solution of the issue. You can rest assured that it's much appreciated.

 

[USAFRet]

Excellent. Now you have an actual database, that you can expand on, and have data type verification.