Merging two worksheets into one.

Hi there,

I'm the IT guy for a non-profit. I have two separate worksheets. One is our "master list" of donors, which contains the first and last names, addresses, amount donated, date donated, etc. of all our donors, each in separate columns. On that worksheet, there are about 1500 rows missing the amount donated value. I have another worksheet which contains about 900 first and last names and amount donated rows. All of the names in the 2nd worksheet are listed in the 1st worksheet but have no donation amount tied to them. Is there a way to merge these two worksheets so that the donation amount information information from the 2nd worksheet is put into the corresponding donor on the 1st worksheet?

I've attached a rough example of the worksheets I'm dealing with. Unfortunately I can't provide the actual worksheets as they include private donor information.

Thank you so much!

Hello seanherron,

Welcome to the Forum!

Here is a dynamic macro to fill in the information. It uses the first matching name to fill in the blanks on the Master worksheet. If the name of the sheets or columns are different in the actual workbook then some variables will need to updated in macro. If you aren't comfortable doing this, let me know what the sheet names are and which columns you are using and I will make the changes for you. Here is the macro code...

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Hi seanherron,

I think the way you want is not the must correct way to approach the problem. You may have to deal with the situation that 1 donator can donate more than once... or even that have a donator not registered in the "Master Donor List"

Pls check the file attached

Originally Posted by Leith Ross

Hello seanherron,

Welcome to the Forum!

Here is a dynamic macro to fill in the information. It uses the first matching name to fill in the blanks on the Master worksheet. If the name of the sheets or columns are different in the actual workbook then some variables will need to updated in macro. If you aren't comfortable doing this, let me know what the sheet names are and which columns you are using and I will make the changes for you. Here is the macro code...

Hi Leith,

Thanks for your help. I ran the macro (I just had to change the sheet names from your example). It filled in about 90 of the values out of the total of 500 I have. Is there a reason as to why it wouldn't put the other 400 in?

Thanks,
Sean

Hello seanherron,

The macro will fill in the name only once. If you have multiple donations by the same person, they won't be filled in. I wrote this using your example and forgot to account for that contingency. I can correct this. In the mean time, have you looked at Jokacave's example?

Sincerely,
Leith Ross

Originally Posted by Leith Ross

Hello seanherron,

The macro will fill in the name only once. If you have multiple donations by the same person, they won't be filled in. I wrote this using your example and forgot to account for that contingency. I can correct this. In the mean time, have you looked at Jokacave's example?

Sincerely,
Leith Ross

Hi,

The macro filled in seemingly random people, however. There are very few duplicates on the list so there might be some other factor at play.


I looked at Jokacave's example but didn't really understand it well. I'm not too concerned about duplicates at this point because we are soon going to hopefully move to Access. I just want to get all our information into one spreadsheet before we do it to minimize complications.

Thanks,
Sean

Hello Sean,

At the moment, I have no explanation for the behavior. Can you post a workbook with more names? It is difficult to troubleshoot a problem like this one when the original sample was so small.

Sincerely,
Leith Ross