sync two workbooks with respect to cell content matching

Posted by Tan Le on September 11, 2001 2:40 PM

Hi Richard,

I am so glad that I you are helping me. Your answer is better than VBA. But I need more help. I can't get it to work. How do name the range "header"? Is is just typing the titles in row 1 for every column? I re-iterate the problem below and incorporate your formula. Please see if that is correct!

I have two Excel workbooks. Book1 has two columns: Symbol (col. A) and Industry (col.B). The Book2 has also two columns: Symbol (col. A) and Description (col. B). As you can see both workbooks have the same Symbol because the contents of Symbols are identical.
----------------------------
Book1.xls:

1 Symbol Industry
2 CORN FOOD
3 OAT WHEAT
4 CARROT VEGE
.
.
.
.

---------------------
Book2.xls:

1 Symbol Description
2 CORN IT'S A YELLOW SEED
3 CARROT IT'S A RED VEGE
4 OAT IT'S A BROWN GRAIN
.
.
.

-------------------------
The goal is to make book1.xls to look like this:

1 Symbol Industry Description
2 CORN FOOD IT'S YELLOW SEED
3 OAT WHEAT IT'S BROWN GRAIN
4 CARROT VEGE IT'S RED VEGE

You suggest to put the equation in book1.xls cell C2 equal to:
=IF(ISERROR(MATCH(A2,Book1.xls!Symbol,0)=TRUE),0,HLOOKUP($B$2,Book2!Description,(MATCH(A2,Book1!Symbol,0))))

I tried..it returns 0, any idea?

Best regards,

Posted by Richard S on September 11, 2001 4:04 PM

See below at your original post

Posted by Richard S on September 11, 2001 4:06 PM

Aladin?

Aladin,

Your post got messed up below. I get Craigs post above. I would like to see your comments or suggestion. Always trying to learn.

Richard Hi Richard,

Posted by Aladin Akyurek on September 11, 2001 4:41 PM

Re: Aladin?

Your post got messed up below. I get Craigs post above.

Yep. The "pesky" script here [I'm sorry Bill] is acting up again,

> I would like to see your comments or suggestion. Always trying to learn.

I thought "Richard is maybe not around. Why not give a hand?" Since you're asking, the following (approximately) was/is what I tried to post.

------
I'll assume the data in sheet X of Book1.xls to be in the range A1:B4 and the data in sheet Y of Book2.xls in the range A1:B4.

In sheet Y (Book2.xls),

select the range A2:B4 (excluding "headers", that is, the column headings) and type SDATA (from source data) in the Name Box on the Formula Bar.

In Sheet X (Book1.xls),

in C1 enter: Description
in C2 enter: =IF(ISNA(VLOOKUP(A2,'Book2.xls'!SDATA,2,0)),"",VLOOKUP(A2,'Book2.xls'!SDATA,2,0))

This formula is a frequently used to accomplish the task at hand. It returns a description associated with the value of A2 or a blank ("") if the value of A2 is not available in SDATA.

A fancier formula with MATCH can also be used. In order to have this one, do the following.

In Y (Book2.xls),

select the range A2:A4 (excluding the column heading) and type SYMBOLS in the Name Box on the Formula Bar.

Now, in X (Book1.xls), instead of the above VLOOKUP formula,

in C2 enter: =IF(ISNUMBER(MATCH(A2,'Book2.xls'!SYMBOLS,0)),VLOOKUP(A2,'Book2.xls'!SDATA,2,0),"")

Note. Richard -- I never use ISERROR in this context because it'd catch any error, so conceal problems that we would not want to ignore.

A more fancier formula would also compute the number 2 that appears in the VLOOKUP part. But I'll omit that here.

As you see, nothing special.

Aladin

: ---------------------

Posted by Tan Le on September 12, 2001 12:33 PM

Re: Aladin?

Aladin, Richard, Craig,

The formula + name function SDATA WORKS!!!!!!! I cannot believe this replaces lines and lines of VBA.

THANK YOU!!! I will be back for more questions.

=IF(ISNUMBER(MATCH(A2,'Book2.xls'!SYMBOLS,0)),VLOOKUP(A2,'Book2.xls'!SDATA,2,0),"")

: Richard