Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for data comparison/validation?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for data comparison/validation?
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. "Linda1949" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for data comparison/validation?
Thanks Martin! That worked perfectly and I see how I was making errors with
the VLOOKUP function. You saved me hours of work! "Martin Fishlock" wrote: 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. "Linda1949" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |