Macro to combine two rows with the same id

**Luden** · 09-18-2019, 04:02 PM

Hello all,
Here is a sample of a large data set:
Capture.PNG

I want to combine two rows with the same id into one row. There are three types Primary, No record and Spouse. When combining, the primary and no record should always be at the left. Also, different people have different numbers of phone#, like John has four and Becky has seven and others might have 10. All the combined spouse should be placed after the longest row, like in the sample after seven. Here is the result:
Capture1.PNG
Capture2.PNG

I'm wondering if that is possible. Thank you for your help

**alansidman** · 09-18-2019, 05:09 PM

Please Login or Register  to view this content.

**Luden** · 09-18-2019, 06:47 PM

Originally Posted by alansidman

Please Login or Register  to view this content.

Hello Alan,
I tried your code, but still when the primary is one row under the spouse, the primary goes behind.
Also, I have people who have like 17 phone numbers. The code overwrites the data behind phone number 7. Thanks
e1.PNG
e2.PNG

**alansidman** · 09-18-2019, 10:20 PM

How about this adaption?

Please Login or Register  to view this content.

**Luden** · 09-18-2019, 11:16 PM

Originally Posted by alansidman

How about this adaption?

Please Login or Register  to view this content.

Thank you, Allen. The primary part is ok and the combining doesn't overwrite the data. Now is it possible to move all the spouses to cells right after the RIGHTMOST column? Say I have a person who have 17 phone numbers, meaning the rightmost column is called Phone Number 17. Then all the data starting from spouse go after the column Phone Number 17. The words spouse are all in one column and don't overwrite the data.
I'm trying to be specific, but maybe too complicated lol

**alansidman** · 09-18-2019, 11:27 PM

Because we have now appended the spouse data to the next available column for each row, I really have no way of knowing where the longest row for the Primary is located. Let me think about a workaround to determine the widest record before cutting and pasting. Getting late here and will look at this later when I have time. It is now sleepy time.

**alansidman** · 09-18-2019, 11:35 PM

Had a brain spurt before getting off the couch.

Please Login or Register  to view this content.

**Luden** · 09-19-2019, 10:13 AM

Originally Posted by alansidman

Had a brain spurt before getting off the couch.

Please Login or Register  to view this content.

Wow, works like a charm. Thank you so much, Allen!

BTW, should include lcc As Long

**Luden** · 09-19-2019, 11:02 AM

Originally Posted by alansidman

Had a brain spurt before getting off the couch.

Please Login or Register  to view this content.

Sorry Allen,
When I carefully test your code, I found that when combining primary and spouse, even though the spouse has like 6 phone numbers, it only shows four numbers.
All the spouses have three or four phone numbers no matter how many phone numbers they originally have.
If Mary has 7 numbers:
Capture1.PNG
After combining, there are only five numbers:
Capture2.PNG

Is that because you didn't define lcc? When I executed the code, I put lcc As Long. Maybe that's the problem? Thank you!

**alansidman** · 09-19-2019, 08:16 PM

Forgot to define lcc. Good on you for finding that. Long is correct. Once you did that, did the code run appropriately or was there still an issue.

**Luden** · 09-23-2019, 10:39 AM

Originally Posted by alansidman

Forgot to define lcc. Good on you for finding that. Long is correct. Once you did that, did the code run appropriately or was there still an issue.

Hello Allen,

Sorry for the late reply. I was able to troubleshoot the problem. Thank you anyway.

**alansidman** · 09-23-2019, 02:21 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Macro to combine two rows with the same id

LinkBack

Thread Tools

Rate This Thread

Display

Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Re: Macro to combine two rows with the same id

Thread Information

Users Browsing this Thread

Similar Threads

Macro to combine multiple rows in to one row

Macro to Combine Rows of Data

[SOLVED] Help! Macro: Combine rows with 'same name entry'

[SOLVED] Help with macro to combine rows

[SOLVED] Macro to combine rows

[SOLVED] Help with a macro to combine rows in a spreadsheet

Macro to combine Rows

macro to combine rows and add numbers

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions