Search two columns for identical matches on button press macro

I have a worksheet where in each of the first two columns a 7 digit order number will be placed. Eventually each number entered will be entered in both columns, rarely will they be in the same row. For instance, say order number '6541236' was entered in column B in row 3 and then at a later time the same order number was entered in column A but in row 406.

The goal being that when we switch to the next sheet we can activate a macro by clicking on a cell. We want the macro to search both column A and B for any matched 7 digit order numbers between them and then report those matched values in a single list on the new sheet. There might only be 1 matched pair or there could be many.

I'm also trying to build another macro that will clear any cells in column A and column B that have matches (the ones previously reported) so they are not still in my table after they have already been accounted for.

I've tried using LOOKUP and similar functions but from what I'm reading the way to do this with the clickable button/cell is through using a VBA macro. Any help would be appreciated and if any further clarification is needed I can elaborate.

Thanks!

Lots of formula-based solutions in this thread from earlier today:

http://www.excelforum.com/excel-form...-column-b.html

Hope this helps.

Pete

Thanks Pete!

However after reading and doing more research what I need is how to do this using VBA. I want to be able to click a button and have the macro execute so that is looks through column A and column B for identical matches, then have it list the numbers that had matches on the next sheet.