Compare and Update elements from Sheet1 with Sheet2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

joel.agustin@gmail.com - 26 Nov 2007 16:57 GMT

Hello,

Problem: I have 2 spreadsheets. The first spreadsheet (SpreadsheetA)
is the master record of our inventory elements. The second spreadsheet
(SpreadsheetB) is a copy and paste of some of the elements in
SpreadsheetA but have been updated in some way. Is there a script of
sorts that will find the serial numbers of B in sheet A and update the
appropriate column.

For example.
SpreadsheetA

c1 c2 c3 c4 c5
r1 001 ABC
r2 002 BCD
r3 003 CDE
r4 004 DEF
r4 005 EFG

SpreadsheetB

c1 c2 c3 c4 c5
r1 001 XYZ
r2 002 WED
r3 003 DF4
r4 004 VR#
r4 005 POE

I want to take c2 from SpreadsheetB and find the corresponding record
in SpreadsheetA and update the elements of c3.

Any ideas? Even a partial script would be very helpful!

Joel

Reply to this Message

Bernard Liengme - 26 Nov 2007 21:54 GMT

Look in Help under VLOOKUP and come back if more detailed helps is needed
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hello,
>
[quoted text clipped - 30 lines]
>
> Joel

Reply to this Message

Bernard Liengme - 26 Nov 2007 21:57 GMT

I was being lazy:
=VLOOKUP(A1,Sheet1!A1:B100,2,FALSE)
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hello,
>
[quoted text clipped - 30 lines]
>
> Joel

Reply to this Message

joel.agustin@gmail.com - 27 Nov 2007 16:27 GMT

Hi Bernard,

Thanks for the reply! I ended up using Index and Match instead of
Vlookup since I needed to return the value of another cell. At the end
of it all my formula looks like this:

=IF(ISNA(MATCH( <item_searching_for> , <column_looked_in> ,
0)),"",INDEX( <sheet_looked_in> ,MATCH(<item_searching_for> ,
<column_looked_in> ,0),8))

In English: Find for item in a column of Sheet1 in a column of Sheet2
(Match function returns a row #). If it returns an error, then it's
not found and return a blank. If it returns a row #, then give me cell
contents at the intersection of that row # and a constant column.

=IF(ISNA(MATCH('IS Inv'!J2,Temp!K:K,0)),"",INDEX(Temp!
$1:$65536,MATCH('IS Inv'!J2,Temp!K:K,0),8))

This appears very complicated and I'm sure it can be broken down into
an easier and shorter formula, but it works for me! I hope this helps
anyone else who is doing a similar process.

Thanks again Bernard, for pointing me in the right direction.
Joel

Reply to this Message