excell speadsheet help

[wegotitugetit]

basically i have a spread sheet and i need to make stock levels adjustment each week unfortunatley i hav 20,000 items on this spreadsheet and it is causing me a headache bit time

i have several headers

item name , cost price , part number , retail price

so basically the item does not currently have stock levels but i will be reciving a spreadsheet with only current stock so lets say for example out of the 20,000 i recive onyl 15,000 in stock i need to make this so i can say part number xxx has 1 stock part number xxx1 has no stock but needs to be done quickly i can not sit there every week and look and adjust the numbers every time it will be a nightmare

 

[joesmith]

That seems like a bit too much data for a spreadsheet, imho you would be much much better off setting up a database. Creating a database in Access is pretty straight forward and once you have set it up it should save you a lot of trouble. You can set up queries to find out information like the example you have given pretty easily and it's very quick. It does mean learning a new piece of software if you haven't used Access before though.

 

[troutfisher]

If I am reading this correctly at the moment you have ,or intend to have a column labelled stock level in your existing spreadsheet so you will be able to perform a calculation which will give you the stock value,both by item and total value.
At the moment you do not have any figures for stock level but these are going to be supplied so you will have part number and number of items.
Its a long time since I used excel but I can think of two ways.
You could sort both spreadsheets by part number so they are both in the same order and then copy the stock numbers from one to the other.
You would have to ensure that if the number on your recd spreadsheet is the result of a calculation then you do 'paste special' so you paste the number not the calculation.
eg If the figure on your recd sheet is the result of A-B then you paste the result not the formulae that gave the result.
The other option and regrettably I cannot remember how to do it is the create a macro using VLOOKUP to copy the result from one spreadsheet to another and step down one row at a time.
I used to do these on a regular basis but unfortunately age and lack of use have taken their toll and I dont think I could do it now.
Best of luck with it.

Chris

 

[sep9001]

If you have a unique id on both spreadsheets and the supply spreadsheet will be in the same format you can use a vlookup.

If you want, I can set up the vlookup for you if you send me an example spreadsheet.

 

[wegotitugetit]

so many ways lol
i need to work out which is best

once done will then have to be trasnfered into ebay and amazon once done as csv files and this is just one supplier

vlookup might be a good option but this has to be done im multiples

 

[sep9001]

so many ways lol
i need to work out which is best

once done will then have to be trasnfered into ebay and amazon once done as csv files and this is just one supplier

vlookup might be a good option but this has to be done im multiples

Once it is in a spreadsheet, you can save as CSV. If you have an example it may help in working out the best way to do it.

 

[wegotitugetit]

Once it is in a spreadsheet, you can save as CSV. If you have an example it may help in working out the best way to do it.

i already have it as both csv and excell but that is not the issue the current stock is 1000 on all items and now i have reduce the items to correct amount and remove the ones not listed anymore and ones we can not get aswell

will post one soon

 

[wegotitugetit]

this is the original file from the supplier i have to then add image url , stock quatity there is so much to add
for amazon i roughly fill in around 20 + boxes so bit of a nightmare so this picture is only part of it
the file i will recive once a week jsut gives me the manufactures part no and that is it , so the part number just tells me this is in stock so i make a quantity of what ever i need to make it work



01 by wegotitugetit, on Flickr

 

[sep9001]

Do you have the universal product code on your spreadsheet, also is this a unique to each product?

I don't have excel on my laptop but will try and create a dummy sheet with the formula when at work tomorrow morning.