• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

EXCEL gurus look here!!!

I

imported_Pablo

Diamond Member
Next week, I am going to have to do inventory for my company. We stock about 5000 parts from many manufacturers, whose prices change from year to year.

Our vendors supply price lists in excel format. For example, first column is part number, second column is description, and third column is price.



My question is: Is there a way to copy the new prices to our inventory spreadsheet easily, searching for part numbers and updating prices accordingly?

Sorry if this makes no sense... 🙁
 
You fail to mention the format of your inventory spreadsheet. If it's XLS also, then I don't see any problems.

An even better way is to use an RDBMS... MS Access would be a good cheap start.
 
Sort both your file and the vendors file by part number. Do they match up perfectly? Each one has exactly the same list of part numbers? Then just cut and paste the price column from one file to the other.
 
It is in .xls format... however, I can't figure out how to get excel to look thru our spreadsheet, find part numbers, and then search and update the prices from the vendor's pricelist spreadsheet....

(i'm looking into Access as well... any clue how many part#s it could handle?

No they do not match up perfectly... i wish they did 😀
 
Access is scaleable and there is no reasonable limit the amount of data it can hold it the database is designed properly. Although not optimized for large scale applications (which Oracle and the like is better suited for), large scale applications as far as Access is concerned would be in the range of 5-10 databases with approx 5-25k records a piece.

In short, Access will do what you want if you tell it the right way (ie: ask nicely with twinkie in hand).
 
You can use queries with Excel too.
Data -> Import External Data -> New Database Query

😀 or search for query in help.
 
Originally posted by: PabloMartinez
awesome guys... thanks for the info....

is it easy to import an excel pricelist into Access?
Click to expand...
Yup, just create a new access db and you can "Get External Data" which would be your XLS. There's a wizard to help you along.

 
If the list of parts that differ is small... say under 20 or so, you could easily just edit the spreadsheet by hand to accomodate the changes in the inventory, then cut and paste. MS Access is a more elegant solution, and certainly has more potential for future work, BUT if all you are doing is a 1-time spreadsheet, AND the changes in parts isn't a big deal, hten I would think that its easier to use EXCEL.

 
You must log in or register to reply here.

TRENDING THREADS

Back
Top