Data Verification

**LM100** · 09-14-2008, 07:19 AM

Hi.

I have two worksheets. The first is a bit messy due to user error when inputting. It looks something like this

Please Login or Register  to view this content.

I need to correct these descriptions with the second worksheet data which has the accurate description for all lines, for example:

Please Login or Register  to view this content.

To manually correct all of the first sheet would be a tedious task as there are a good 18-19 thousand rows of data.

I should mention the first sheet is a 97-03 spread sheet and the second is a 07 excel spread sheet (as the second as over 100 thousand rows of data)

How can I do this?

**Richard Schollar** · 09-14-2008, 08:01 AM

Hi

Use a Vlookup in the first spreadsheet in an adjacent column (eg col C) along the lines of:

=VLOOKUP(A2,'[SecondWorkbook]Sheet1'!A:B,2,0)

and copy down (adjust ranges to suit if not in A & B columns). This should provide you with the corrected result. Then copy and pasteSpecial>Values over your original values. Following this you can delete the formula column.

Richard

**LM100** · 09-14-2008, 08:09 AM

Hi Richard,

Thanks for your reply.

I can't seem to adjust the formula for my data, could you do this for me?

In my first sheet the "line" column is E and "description" F
my second sheet is called "assortment.xlsx" and have its "line" column as A and description as B

Thank you!

**Richard Schollar** · 09-14-2008, 08:33 AM

With both files open in xl2007 it would be:

=VLOOKUP(E2,'[assortment.xlsx]Sheet1'!A:B,2,0)

You may need to adjust the sheet name (from Sheet1) to whatever it is in your second (xlsx) sheet.

Richard