Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 10th 05, 05:46 PM posted to microsoft.public.excel.worksheet.functions
AndreaW
 
Posts: n/a
Default 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   Report Post  
Old December 10th 05, 06:01 PM posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to swap rows and columns? [email protected] Excel Discussion (Misc queries) 5 September 21st 05 08:07 AM
How do I sort (or group?) a spreadsheet by similar rows? steve Excel Worksheet Functions 3 August 27th 05 06:33 PM
insert rows and update linked cells Laura Excel Discussion (Misc queries) 2 August 26th 05 07:52 PM
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM


All times are GMT +1. The time now is 06:44 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017