I've been attempting to write a macro to do a comparison with a copy function when a match is found. Hopefully this helps explain it: Situation:

Worksheet1 column A is always the same and has the same values (can-1, letter-4, etc).

Worksheet2 is imported via a macro and always has a different variety of the values of worksheet1 column A (In column A of Worksheet 2) plus additional column B with a quantity.Worksheet1 has a set number of 500 rows.

Worksheet2 may contain anywhere from 5 to 200 rows.Both worksheet values start on the same column row A5. (If that makes a difference)

Goal:

I'm trying to search Worksheet2 Column A for matches with Worksheet1 Column A and when there is a match copy the quantity from Worksheet2 Column B to Worksheet1 Column B.Example:

Worksheet1

Column A Column B

can-1

letter-4

apple-94

litter-0

nuts-462Worksheet2

Column A Column B

apple-94 4

nuts-462 1

Goal Worksheet:

Worksheet1Column A Column B

can-1

letter-4

apple-94 4

litter-0

nuts-462 1I hope I've given enough details. Any assistance is greatly appreciated. So far I just keep replicating the #value. So I know I'm missing something.

A =VLOOKUP() should get you what your looking for. With your Sheet 1 like:

A B 1) can-1 2) letter-4 3) apple-94 4) litter-0 5) nuts-462In cell B1 enter the formula:

=IF(ISERROR(VLOOKUP(A1,Worksheet2!$A$1:$B$2,2,0)),"",VLOOKUP(A1,Worksheet2!$A$1:$B$2,2,0))

You should get this:

A B 1) can-1 2) letter-4 3) apple-94 4 4) litter-0 5) nuts-462 1You only gave two rows of data on Sheet 2,

If you have more than two rows of data on Sheet 2 you will need to modify

the formula and change the ranges.You specified the sheet names as Worksheet1 & Worksheet2,

If the sheet names are different you will need to modify the formula and change the

sheet names.See how that works for you.

MIKE

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History