merge two worksheets

**bluntz** · 05-17-2009, 02:27 PM

I have two worksheets, one is called "donors" and the other "donations". On the "donors" worksheet, I have a list of people who have donated money to our organization, each person with a unique donor ID number.

On the "donations" worksheet, it's just a list of all donations made along with the unique donor ID next to each donation. This list has alot of duplicate ID's, since for example one person can give 10 times in one year.

I am building a macro which will use a nested loop to first look up an ID in the "donors" sheet, then go to the "donations" sheet and loop through all the rows, looking for that ID. If it finds a match, it will copy two cells from that row, and then paste them to the end of the row on the "donors" sheet. It will continue looping through all donations, looking for additional matches, and if it finds any will repeat the same actions. After it finishes this, it moves on to the next "donors" ID.

I have some of the programming down, and I've confirmed that the nested loop functions as is, but I just don't know how to get it to copy and paste the cells from one sheet to the end of the row on the other sheet.

Here is the code I have so far:

Please Login or Register  to view this content.

Can anyone help me finish this one off? Thanks alot!

**stanleydgromjr** · 05-17-2009, 05:12 PM

bluntz,

Please post your workbook (or a sample copy) - scroll down and see "Manage Attachments".

**bluntz** · 05-18-2009, 12:56 AM

I've attached a sample workbook...the data is arbitrary, just so you get the concept...i have two sheets, one with about 22,000 donors, each with name, address, phone, donor ID. on the second sheet i have about 24,000 donations which were made, each with donor ID, amount, date.

the donations list has alot of duplicate donor ID's, since people make donations multiple times. the donors list has no duplicate ID's, since each row is a unique person.

for example i need to look at John Doe's donor ID on row 5, column B of the donors sheet, and then loop through all the donations made on the donations sheet. if i find a donation where the donor ID is the same as John Doe's donor ID, it should add the amount and date to row 5, column B of the donors sheet.

**JBeaucaire** · 05-18-2009, 02:52 AM

You want the donations accumulated count to replace the ID number in column B? Did you mean to say that? Or do you want a new column E with the donations accumulated in it?

**JBeaucaire** · 05-18-2009, 03:09 AM

Here's a simple macro for your task. It includes an error check on the IDs, if a donation does not match the Donors list anywhere it will highlight that ID in the Donations list for your later review.

Please Login or Register  to view this content.

**bluntz** · 05-19-2009, 02:58 AM

Ok JBeau once again you've helped me fix my macro issue

Using my previous code and incorporating some of yours, I came up with this:

Please Login or Register  to view this content.

It works extremely efficiently but I'm sure I could optimize the appending part in the middle, I just don't know how. As I type this though, I'm thinking it would have been easier just to make it look for the last cell in the row with data in it. How do you do that? Or if anyone knows a better way, I'd love to see it. Thanks again for the help! SOLVED!