Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Update rows with new pricing
I receive daily updates from various suppliers. Instead of dragging and
dropping their new data into my one master price list, I would like to have Excel look up and replace the old prices with the new prices into my list for each supplier. Is there an easier way of doing this instead of doing so manually (drag and drop)? The daily updates can be anywhere from 5 entries to over 3,000. 5 is ok to do manually, but 3,000...yikes! I have 2 columns within my lists which are identical to 2 columns from each of my suppliers. Each supplier's price list contains approximately 8 columns. I need to ensure I replace the data into the proper row and thought Excel could perform this tedious task in short order. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Update rows with new pricing
You can use the vlookup function to get new prices for existing SKUs from the
updated table. Suppose your master list is on a worksheet named PL, with p/n in column A (starting in row 2) and existing price in column B; the new data is on a worksheet named Update, with the same columns. (You mention that the actual columns on the update vary, so you may have to change the 2nd and 3rd arguments of the vlookup function below) In PL!c2: =if(isna(match(a2,Update!A:A,false)),b2,vlookup(a2 ,Update!A:B,2,false)) This will check if this item is in the update. If so, it retrieves the new price; otherwise it retains the old price. Autofill this through column C. Then copy column C, select column B and Edit Paste Special values, and finally delete column C. (If you want to first see the updates that would be applied, you could just use =vlookup(a2,Update!A:B,2,false); where the item isn't on the update list, you'll see #N/A. Hope this gets you started... --Bruce "AndreaW" wrote: I receive daily updates from various suppliers. Instead of dragging and dropping their new data into my one master price list, I would like to have Excel look up and replace the old prices with the new prices into my list for each supplier. Is there an easier way of doing this instead of doing so manually (drag and drop)? The daily updates can be anywhere from 5 entries to over 3,000. 5 is ok to do manually, but 3,000...yikes! I have 2 columns within my lists which are identical to 2 columns from each of my suppliers. Each supplier's price list contains approximately 8 columns. I need to ensure I replace the data into the proper row and thought Excel could perform this tedious task in short order. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
How do I sort (or group?) a spreadsheet by similar rows? | Excel Worksheet Functions | |||
insert rows and update linked cells | Excel Discussion (Misc queries) | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |