May 29 2008, 10:35 AM
We get many of our price files in excel format from our vendors. I have a list of about 2000-3000 products with the part numbers and prices that need to be updated. My vendor has supplied us with a new price sheet and we want to update the prices of the products, however there are new products in the new price sheet.
So instead of there being say... 2100 lines (like there is in the old price sheet) there are 2200 lines (in the new sheet)
I am trying to figure out a way that I can update all of the prices quickly so I do not have to do them 1 by 1. Is there a program or formula that will search both sheets ... maybe the part number column and if the part number matches in both sheets ...update the price? Hopefully that wasn't too confusing.
Monster Tech Lead
May 29 2008, 11:39 AM
Currently I don't know of a simple way to do this but you could do it with a pivot table I believe in excel.
Otherwise the easiest way that I know how to do this would be with access or a SQL DB.
You would first import the products- say part numbers and prices that you currently have into a table (tbl1).
You would then import the new listings into a second table (tbl2).
Finally you could then run a query to join those two tables by part number - select tbl1.partnumber, tbl2.price from tbl1 join tbl2 on tbl1.partnumber = tbl2.partnumber;
The list that it gives you - you simply copy out and then paste into your spread sheet. Then once you have this saved in access you just need to remove all old data from both tables each time and reimport the new data.
The other option for this would be to write a script that puts all the part numbers and prices into an array, you would then run a comparison for the part numbers and if you get a match to update the price.
May 29 2008, 02:32 PM
I have the same situation with over 5.000 products updated every 60 days. I use a program from ablebits.com called Merge Tables Wizard. It will do what you need without having to learn Access. It works like a champ for me.
May 29 2008, 06:08 PM
Filemaker Pro for Windows (or Mac), will do it.
We use that feature, every other day for a couple of vendors
May 30 2008, 03:02 AM
Thank you Both!
Nov 17 2008, 11:15 PM
Thank you, for weeks I am breaking my head how to update my custom feeds to Google Base , Yahoo shopping and MSN. We constantly change prices and Hide / UNHIDE products. Building a feed from scratch is too time consuming because some of the columns are not on the download from Networksolution. (UPC , custom categories etc.)
The Merge Tables Wizard from AbelBits looks like a perfect solution! I will play with it tomorrow. with this solution I can build a master feed including url markings and ask the program to:
Eliminate hidden or out of stock products!
what a find! Thanks again
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here