How to transpose non-uniform data in multiple columns into multiple rows?

**alexxgalaxy** · 01-03-2013, 10:32 AM

Hi

I have a large amount of non-uniform data that I need to alter from multiple columns to multiple row.
See example in Part A to Part B (Sometimes the data can spread over 20 columns for the same row). Is there any Macro that can transpose the data in the form of Part A to the form of Part B? Thanks.

Part A
Column 1,Column 2,Column 3,Column 4,Column 5,Column 6, Column 7
Team A,Jimmy,Peter,May,Mary,Ivy,Alex
Team B,Ryan,Ken,Julia
Team C,John,Noel,May,Kristyn
Team D,Mark

Part B
Column 1,Column 2,
Team A,Jimmy,
Team A,Peter
Team A,May
Team A,Mary
Team A,Ivy
Team A,Alex
Team B,Ryan
Team B,Ken
Team B,Julia
Team C,John
Team C,Noel
Team C,May
Team C,Kristyn
Team D,Mark

**JOHN H. DAVIS** · 01-03-2013, 03:41 PM

Maybe:

Please Login or Register  to view this content.

**mike7952** · 01-03-2013, 04:28 PM

Another way

Please Login or Register  to view this content.

**alexxgalaxy** · 01-03-2013, 08:35 PM

Hi John

This works well for the example I gave but it didn't satisfy the situation that I mentioned "Sometimes the data can spread over 20 columns for the same row". Thanks for so much for the reply, though!!

**alexxgalaxy** · 01-03-2013, 08:40 PM

Hi Mike

This works perfect! And the syntax was a lot shorter than I expected. Also simpler, I might be able to understand how it works.

Thank you so much!!!