syncing business product sheets

I run a small home business. my problem is this, i use/sell about 100-150 products form a particular distributor i go through, but their product detail sheet contains all 20,000+ products that they have.

here is my problem, my data sheet contains about 10 different columns for different info such as pricing, description, etc.

of course prices change daily, i physically have to go through each cell and change the price according to the distributor.

is there anyway to "sync" up the two data sheets to where MY product page will sync with distributor.

for example, if i have product # 1 named "12341234" and it is priced 29.99, but the distributor prices drops, how do i sync that data with the distributor data, i would just want the things listed in column one to sync with like items from distributor.

sorry if it sounds confusing, trying to word it out in my head as i go along.

i attached 2 data sheets.

the one labeled "distributor list" is just a brief example. the distributor will have/does have 20,000 + products on that sheet.

the one labeled "product" would be my data sheet.

basically my 2 main goals, there are more, but my two main goals are to automatically update my "product" data with the "distributor list" data for such fields as "price" and "retail price"

i just want to be-able to sync my 100-300 items with the distributors 20,000 simply 1-5 times a week.

the 2cd thing i want to do that wouldn't be hard is to add extensions to a whole column, for example, under "image" lets say the image is named 1234, and it is a jpeg, the server reads the data sheet but has to read it with the extension so it would have to read 1234.jpeg.

so how would i take a whole column and make every cell add on ".jpg"?

thanks

sorry i dont think attachments worked,
took screen shot.

any help? please look over, uploaded the files that were recommended.

If you can upload the sample sheets I could punch in a simple vlookup command for you. If you are having trouble attaching them, make sure they are closed when you try to attach them. I was having trouble with attachments once and once I closed the spreadsheets they attached to the post just fine.

here we go, files are attached.

I get what you are trying to do and as Palmetto suggested vlookup should work, unfortunately the jpgs don't tell much " a picture is worth a thousand words but a workbook is worth a thousand pictures" and all that, would need to see a sample from the workbook- mock one up with a couple of lines on each sheet perhaps if the original is too large?

Originally Posted by somesoldiers

I get what you are trying to do and as Palmetto suggested vlookup should work, unfortunately the jpgs don't tell much " a picture is worth a thousand words but a workbook is worth a thousand pictures" and all that, would need to see a sample from the workbook- mock one up with a couple of lines on each sheet perhaps if the original is too large?

see post right above you!

yes it works, but if you could explain how "vlookup" works, i dont like just doing stuff, especially when it comes to business, without knowing exactly the science behind it, and am i going to have to do this for every piece of data?

and the other problem. i need a solution that is NOT unique to the cell but unique to the upc code or actual data entered in cell because every new distributor sheet that comes out is in a different order.

okay i was thinking the individual cell was the unique part of the formula, not the column.

i have to go for a little bit, i will be back on, but thanks for your help. i won't forget and i will make note of the help i received.

If you save these 2 files into C:\temp2\ then it should work. Like somesoldiers, I linked it using the description. I would recommend taking the time to read up on VLOOKUP so that you can easily edit the formulas that people are sending you. Mine works by reading from the other file, and there is a sheet of variables that you can change based on the location of the file, the filename, the sheet name, and the range you want to search (the first column of the range should contain the number/description you are trying to match).

This will only work if the distributor file is OPEN.

okay doing a little bit of research, i understand better that it just says, correct me if i am wrong.

if sheet one has YYY for this data,
then sheet two will enter YYY for the specified area.

=VLOOKUP(B:B,Dist!C:F,4,0)

okay, i understand for the most part so far, but what does the "4" and "0" represent?

and i will be back on later, thanks for the help from you both. its appreciated.

it won't be to to big of a deal over all, but will i have to do this for each on of my products?

The 4 represents the scope of the vlookup, it starts ie Dist sheet column C and counts 4th from the right inclusive, try changing this to 3 or 2 for example and you will see that it pulls in data from different rows. the "0" defines the type of match, exact match or closest match-for your example I'd stick with exact. let us know how you get on.