Merge inventory from one excel spreadsheet to existing sheet?

internetlad

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

USAFRet

Splendid
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


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

 

ex_bubblehead

Honorable
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
0
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
67


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
0
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

Splendid
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


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

Splendid
Moderator


He stated they already have Access.
 

internetlad

Distinguished
Jan 23, 2011
183
0
18,630
0
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.
 
Thread starter Similar threads Forum Replies Date
M Apps General Discussion 1
S Apps General Discussion 1
P Apps General Discussion 14
G Apps General Discussion 2
A Apps General Discussion 3
R Apps General Discussion 3
M Apps General Discussion 1
magnificent_90 Apps General Discussion 1
tomsguideUS Apps General Discussion 0
Q Apps General Discussion 20
C Apps General Discussion 9
S Apps General Discussion 1
E Apps General Discussion 2
viveknayyar007 Apps General Discussion 0
K Apps General Discussion 8
J Apps General Discussion 2
David 617 Apps General Discussion 1
K Apps General Discussion 1
M Apps General Discussion 1
J Apps General Discussion 1

ASK THE COMMUNITY