vba to match and list values & list exceptions

[leigh_ashlin]

Hi there,

I have 2 worksheets with linking data in a common column 'A' for each worksheet.

I need the data in column 'B' in 'sheet2' to be moved into column 'J' of 'sheet1', based on the linking values that are located in column 'A' in both worksheets.

Also, can the values in column 'A' of 'sheet2' that do not match column 'A' of 'sheet1' be listed as 'exceptions' somehow?

Or is this getting to tricky?

Can somebody help me create the vba for this?

 

[Richard Schollar]

Hi Leigh

It sounds to me as if you could do this without VBA using a VLOOKUP formula - is there some reason you specifically want it codes as a macro?

Best regards

Richard

 

[leigh_ashlin]

Can somebody tell me how to do this with a VLOOKUP and list in a seperate column the list of exceptions? I don't understand how you can break it up.

Thanks

 

[leigh_ashlin]

Can somebody help me with this suggested VLOOKUP solution?

I don't understand how the exceptional values can be listed in another column - can somebody please give me an example?

 

[Richard Schollar]

Leigh

Column J formula of Sheet1:

=VLOOKUP(Sheet1!A1,Sheet2!$A$1:$B$1000,2,0)

this will return a #NA if the value in Sheet1!A1 is not present in the A column of Sheet2. You will need to adjust the ranges to suit.

In Sheet2, in the next adjacent available (ie no data currently in it) column use:

=ISNUMBER(MATCH(Sheet2!A1,Sheet1!$A$1:$A$1000,0))

which will return True if the Sheet2!A1 value is also in Sheet1 and False if it isn't. You can Autofilter on this column and select only the Falses which will give you all the A column values in Sheet2 which have no match in Sheet1.

Does this make sense?

Richard

 

[leigh_ashlin]

Thanks Richard, This makes sense for the matching the values in column A in both worksheets, and to list the exceptional values.

However I need the values in column B of worksheet 2 moved to worksheet 1 based on the matching values of column A in both sheets - is there an option (other than cut and paste) that will do this?

 

[Richard Schollar]

Leig

That's exactly what the Vlookup formula I posted does:

=VLOOKUP(Sheet1!A1,Sheet2!$A$1:$B$1000,2,0)

I should have been more specific - in the case of the formula finding a match in the A col of Sheet2, it will return the value in the B column of Sheet2 (only if there's no match will it reurn a #NA).

Richard