Merge Help Needed!!!

[Jim123]

I have 2 spreadsheets that have the colums : Partnumber, description, price.
One is the current version, and the other has updated prices. Actually, the
one with the current data has 2 additional columns, but the first 3 columns
are the same in both.

Here's what I need to do :

Assume A=current, B=new

Compare A to B, updating 'only' the pricing cell in A where there are
identical part numbers.

I also need to know what parts from B are not in A, so that they may be added.

Does anyone know how to do this? I don't care if it's an add-on product -
I'm desparate!

Thanks

[Carim]

Hi,

in Sheet Current in Cell C2 :
=INDEX(New!A2:C20,MATCH(A2:A20,New!A2:A20,0),3)

adjust to your needs ...
HTH
Carim

[Jim123]

Carim,

Worked like a charm. Thanks, I owe you a beer!

"Carim" wrote:

>
> Hi,
>
> in Sheet Current in Cell C2 :
> =INDEX(New!A2:C20,MATCH(A2:A20,New!A2:A20,0),3)
>
> adjust to your needs ...
> HTH
> Carim
>
>
> --
> Carim
> ------------------------------------------------------------------------
> Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
> View this thread: http://www.excelforum.com/showthread...hreadid=532774
>
>

[Patricia Shannon]

This worked beautifully for the first test case I set up. But when I started
looking at it, it can fail if the New table is missing values in such a way
that there is a match, but the matching row in the New table is smaller than
the row of the Current table; this is because the addresses are all
relative. So, eg., when searching for the value for the Current A8, it starts
to search the New table at A8. If the matching value in the New table is in
A7, it won't find it. Even if the New table is not missing values, it could
fail if you happen to have a current table with more than one row with the
same Partnumber.
If the references to the New table (at least the rows) are changed to
absolute (ie, New!$A$2:$A$20 or New!A$2:A$20, and New!A$2:C$20. it will
take care of all cases.
Also, the MATCH(A2:A20 can be changed to MATCH(A2

Of course, Jim, you should save your file before updating it.


"Jim123" wrote:

> Carim,
>
> Worked like a charm. Thanks, I owe you a beer!
>
> "Carim" wrote:
>
> >
> > Hi,
> >
> > in Sheet Current in Cell C2 :
> > =INDEX(New!A2:C20,MATCH(A2:A20,New!A2:A20,0),3)
> >
> > adjust to your needs ...
> > HTH
> > Carim
> >
> >
> > --
> > Carim
> > ------------------------------------------------------------------------
> > Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
> > View this thread: http://www.excelforum.com/showthread...hreadid=532774
> >
> >