VBA macro to match cells according to user selection

[jabutu]

Hi all,

I'm new to the forum and hoping somebody can help me. I've been trying to code the following functions into a macro but can't figure out how to achieve it. I'm stuck on step 3 and would really appreciate some help with this:

Functions:
1) Match the cell values in Worksheet 1, column A, with the values in Worksheet 2, Column A
2) Output the resultant matched rows from Worksheet 1 and 2 into Worksheet 3
....and here's the bit I can't get.....
3) Where there is more than one match in worksheet 2, ask the user to choose which match to output into Worksheet 3

Since that is mighty confusing, here's a simplified dataset to illustrate
Note that I can't change the format of Worksheet 2, which has the "Surname" also shown in the "Forename" column (in bold).

Worksheet 1: Values to be searched for (Col A)

		A	B
1		Forename	Age
2		Bob	22
3		Jannet	56
4		Julie	43
5		John	23
6		Micheal	11
7		Jeff	23

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

Worksheet 2: Data in which to find a match (col A)

		A	B	C
1		Forename	Surname	Shirt Colour
2		Rands
3		Bob	Rands	Blue
4		Jannet	Rands	White
5		Julie	Rands	Yellow
6		John	Rands	Maroon
7		Jamison
8		Micheal	Jamison	Red
9		Jeff	Jamison	Orange
10		Bob	Jamison	Yellow
11		Sarah	Jamison	Black
12		Simon	Jamison	Green
13		Clark	Jamison	Purple
14		Teller
15		Thomas	Teller	White
16		Candy	Teller	Grey
17		Sarah	Teller	Crimson
18		Jane	Teller	Aqua
19		Anette	Teller	Yellow

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Worksheet 3: Output data from Worksheets 1 and 2

		A	B	C	D
1		Forename	Age	Surname	Shirt Colour
2		Bob	22	Jamison	Yellow
3		Jannet	56	Rands	White
4		Julie	43	Rands	Yellow
5		John	23	Rands	Maroon
6		Micheal	11	Jamison	Red
7		Jeff	23	Jamison	Orange

<colgroup><col span="3"><col><col><col></colgroup><tbody>
</tbody>

Notice that the first entry, "Bob", would not be the first match in Worksheet 2 as it is the wrong Surname. This is where user input would be required and I'd ideally like excel to ask the user to select which match the data should be taken from. Ideally this would be a dropdown list or similar.

Thanks a lot for any help you can give!

 

[jabutu]

Well I figured out a way. For anyone else in a similar situation, or anyone just interested, this is how I did it using two separate macros:

Macro 1:
1) Searched Worksheet 2 for a match to the row selected on Worksheet 1 (e.g. Bob)
2) For each match found within worksheet 2, populate a row of cells within Worksheet 1 with the relevant data (First name and Surname here) from Worksheet 2, including the row number of the matched data (e.g. Row 3 = Bob Rands, and Row 10 = Bob Jamison).
3) For a cell within Worksheet 1 and within the currently selected row (row 2 of worksheet 1 in this example), create a cell with list validation and list range equal to the matches recorded in step 2.
End of Macro 1

User input stage:
User selects the correct match in the drop down list created in step 3 above. e.g. Bob Jamison instead of Bob Rands.
End of user input.

Macro 2:
Using the selections defined by the user, with reference to the matched row, populate worksheet 3 with all the required data.
End of Macro 2.

Add in some other efficiency boosting options, like highlighting cells with more than one match, and this has worked very efficiently for me.

Hope this helps someone else out there!