Vlookup for data comparison/validation?

[Guest]

I have a worksheet containing 2 colums of data to be updated in a database.
One column has the key, the other is the data to be updated. After it is
processed, I want to be able to download the entire data base to a second
worksheet and validate that the updates were made. I am only submitting
about 500 updates, but will have to download 34,000 records. I need to use
the key from the first worksheet to find a match on the second worksheet and
if found, give me the key and data on the first worksheet so I can sort and
compare the values.

EX:
worksheet 1:
Coumn 1 Column 2
abc olive
jkl cranberry
qrs pink

worksheet 2:
Column 1 Column 2
abc blue
def green
ghi yellow
jkl purple
mno red
qrs pink

What I want to do is use the value in column 1 from worksheet 1 to find a
match in column 1 from worksheet 2 and if found, put the key and value on
worksheet 1, so I end up with:

Coumn 1 Column 2
abc olive
jkl cranberry
qrs pink
abc blue
jkl purple
qrs pink

Then I could sort this worksheet by column 1 and use the EXACT function to
find where the updates were made correctly.

Someone suggested I could use VLOOKUP to do the first step, but I've tried
about 15 ways to do that with no luck. If I should be using a different
function, or if someone can help me with the VLOOKUP, or a better way to get
to where I want, I sure would appreciate it.

 

[Guest]

Hi Linda 1949,

I think that the suggestion to use vlookup was a good one.

After you have downloaded the database to worksheet 2 I assume it is in
another workbook where I call it book2.xls you you will have to change it and
the database is on sheet1 in that book.

in the orginal sheet in cell C1 enter

=vlookup(a1,'[book2.xls]sheet1'!A:B,2,false)

then copy it down

then you can check cell to cell with column d

if(b1=c1,"","<<<<<")

and copy that down.

 

[Guest]

Thanks Martin! That worked perfectly and I see how I was making errors with
the VLOOKUP function. You saved me hours of work!

Hi Linda 1949,

I think that the suggestion to use vlookup was a good one.

After you have downloaded the database to worksheet 2 I assume it is in
another workbook where I call it book2.xls you you will have to change it and
the database is on sheet1 in that book.

in the orginal sheet in cell C1 enter

=vlookup(a1,'[book2.xls]sheet1'!A:B,2,false)

then copy it down

then you can check cell to cell with column d

if(b1=c1,"","<<<<<")

and copy that down.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

I have a worksheet containing 2 colums of data to be updated in a database.
One column has the key, the other is the data to be updated. After it is
processed, I want to be able to download the entire data base to a second
worksheet and validate that the updates were made. I am only submitting
about 500 updates, but will have to download 34,000 records. I need to use
the key from the first worksheet to find a match on the second worksheet and
if found, give me the key and data on the first worksheet so I can sort and
compare the values.

EX:
worksheet 1:
Coumn 1 Column 2
abc olive
jkl cranberry
qrs pink

worksheet 2:
Column 1 Column 2
abc blue
def green
ghi yellow
jkl purple
mno red
qrs pink

What I want to do is use the value in column 1 from worksheet 1 to find a
match in column 1 from worksheet 2 and if found, put the key and value on
worksheet 1, so I end up with:

Coumn 1 Column 2
abc olive
jkl cranberry
qrs pink
abc blue
jkl purple
qrs pink

Then I could sort this worksheet by column 1 and use the EXACT function to
find where the updates were made correctly.

Someone suggested I could use VLOOKUP to do the first step, but I've tried
about 15 ways to do that with no luck. If I should be using a different
function, or if someone can help me with the VLOOKUP, or a better way to get
to where I want, I sure would appreciate it.