VBA Macro to Transpose Columns to Rows AND Concatenate Afterwards

Hello,

First post: urgent question.

Data looks like this:

Joe, Bob
Madison, Billy
Drew, Larry

Ying, Min
Yong, Mong

Ting, Tong
John, Jacob


Not only do I need to develop a macro to transpose each of these sets (separated by spaces) into columns, but also I need to then combine them into one cell separated by a space.

The key here: to develop a code to concatenate everything into the first cell, but just run it once so that it will know to group together only the ones separated by spaces all the way down to yield the following

Joe, Bob , Madison, Billy , Drew, Larry

Ying, Min , Yong, Mong

Ting, Tong , John, Jacob

Thank you for your post! I am new to the site- is there a way to acknowledge your help to give you credit, etc.?

I found that the following worked better for my purposes:

Please Login or Register  to view this content.