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

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