Merge inventory from one excel spreadsheet to existing sheet?

Status
Not open for further replies.

internetlad

Distinguished
Jan 23, 2011
183
0
18,630
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.
 
Solution
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
iePPPLK.png


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

(Notice only some of the part numbers show)

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


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


Create an Append query:
Here’s the SQL (you can copy paste this)
INSERT INTO Inventory ( PartNum, Cost, Stock )
SELECT...

ex_bubblehead

Distinguished
Moderator
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

Distinguished
Jan 23, 2011
183
0
18,630
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

Estimable
Nov 18, 2014
201
0
5,110


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.
 

internetlad

Distinguished
Jan 23, 2011
183
0
18,630
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

Illustrious
Moderator
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
iePPPLK.png


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

(Notice only some of the part numbers show)

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


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


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
BK63cWv.png

 
Solution

USAFRet

Illustrious
Moderator


He stated they already have Access.
 

internetlad

Distinguished
Jan 23, 2011
183
0
18,630
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.
 
Status
Not open for further replies.