vba to match and list values & list exceptions

leigh_ashlin

Board Regular
Joined
Feb 9, 2005
Messages
74
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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Feb 9, 2005
Messages
74
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

Board Regular
Joined
Feb 9, 2005
Messages
74
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Board Regular
Joined
Feb 9, 2005
Messages
74
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,136,267
Messages
5,674,729
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top