Merging new data
 

I have a master worksheet (WS1), and a new worksheet (WS2) with updated
pricing on about 3K items. I need to put the new pricing from WS2 for each
item into WS1. Not all items have new pricing, so if there is no change, or
the item isn't even listed in WS2, then I need the old price to be left as it
is. I am only concerned with 2 columns of data, Column A being product #,
and Column B being Price.
Simply put, I want excel to look at product A on WS2, and if that product is
on WS1, then I want to update WS1 with the new price.
It would also be very nice if when product A on WS2 was not recognized it
was highlighted or put into a new column or something, since I will need to
add those new items eventually. But most important I need to update pricing
on these items.

--
Louis

Louis,

Let's assume that in worksheet WS1, you have labels in Row 1, and your data
starts in Row 2. In Cell C2 of worksheet WS1, enter the following formula:

=if(iserror(match(a2,ws2!$a$1:$a$5000,0)),b2,index (ws2!$b$1:$b$5000,match(a2,ws2!$a$1:$a$5000,0),1))

Copy this formula down as many rows as you have data for in worksheet WS1.
Make sure that the workbook has been completely calculated, then copy the
prices from Column C of WS1 and do a Paste Special Values into Column B.

Here's what the formula does:

match(a2,ws2!$a$1:$a$5000,0) - Looks in Column A of WS2 to see the the
Product # in Cell A2 appears. If it does appear, the function returns the
row # in which it appears. If it doesn't appear, the function returns the
#REF! error.

iserror(...) - If the "match" function above returns the #REF! error, it
would normally result in the formula as a whole returning the same error.
The "iserror" function prevents that - it returns "TRUE" if the "match"
function returns an error, thereby allowing you to test for the error
condition without being overcome by it.

index(ws2!$b$1:$b$5000,match(...),1) - Now that we know that the Product #
in Cell A2 appears in worksheet WS2, we use the "match" function a second
time to tell the "index" function where to the find the corresponding new
price from column B.

Good luck!

-- David B

"Louis" WS2 for each
item into WS1. Not all items have new pricing, so if there isoft.com
wrote in message
...
I have a master worksheet (WS1), and a new worksheet (WS2) with updated
pricing on about 3K items. I need to put the new pricing no change, or
the item isn't even listed in WS2, then I need the old price to be left as
it
is. I am only concerned with 2 columns of data, Column A being product #,
and Column B being Price.
Simply put, I want excel to look at product A on WS2, and if that product
is
on WS1, then I want to update WS1 with the new price.
It would also be very nice if when product A on WS2 was not recognized it
was highlighted or put into a new column or something, since I will need
to
add those new items eventually. But most important I need to update
pricing
on these items.

--
Louis

Great, what a massive help. I had to make a few tweaks (my worksheets were
named something different and there were other columns in between the actual
two that mattered) but they were easy to figure out.
Thank you again.
--
Louis


"David Benson" wrote:

Louis,

Let's assume that in worksheet WS1, you have labels in Row 1, and your data
starts in Row 2. In Cell C2 of worksheet WS1, enter the following formula:

=if(iserror(match(a2,ws2!$a$1:$a$5000,0)),b2,index (ws2!$b$1:$b$5000,match(a2,ws2!$a$1:$a$5000,0),1))

Copy this formula down as many rows as you have data for in worksheet WS1.
Make sure that the workbook has been completely calculated, then copy the
prices from Column C of WS1 and do a Paste Special Values into Column B.

Here's what the formula does:

match(a2,ws2!$a$1:$a$5000,0) - Looks in Column A of WS2 to see the the
Product # in Cell A2 appears. If it does appear, the function returns the
row # in which it appears. If it doesn't appear, the function returns the
#REF! error.

iserror(...) - If the "match" function above returns the #REF! error, it
would normally result in the formula as a whole returning the same error.
The "iserror" function prevents that - it returns "TRUE" if the "match"
function returns an error, thereby allowing you to test for the error
condition without being overcome by it.

index(ws2!$b$1:$b$5000,match(...),1) - Now that we know that the Product #
in Cell A2 appears in worksheet WS2, we use the "match" function a second
time to tell the "index" function where to the find the corresponding new
price from column B.

Good luck!

-- David B

"Louis" WS2 for each
item into WS1. Not all items have new pricing, so if there isoft.com
wrote in message
...
I have a master worksheet (WS1), and a new worksheet (WS2) with updated
pricing on about 3K items. I need to put the new pricing no change, or
the item isn't even listed in WS2, then I need the old price to be left as
it
is. I am only concerned with 2 columns of data, Column A being product #,
and Column B being Price.
Simply put, I want excel to look at product A on WS2, and if that product
is
on WS1, then I want to update WS1 with the new price.
It would also be very nice if when product A on WS2 was not recognized it
was highlighted or put into a new column or something, since I will need
to
add those new items eventually. But most important I need to update
pricing
on these items.

--
Louis