Excel - converting multiple cells with comma and transposing

[Yella Fella]

I have a dataset that spans around 50+ columns, however the names are all under a cell in row 2 separated by commas. Example:

For A2, you can see the full string as its the same in each cell in E2, I can use Data > Text to Columns and separate this fine into row 3 for instance. Then using the copy and right click and transpose option.

However, is there a better way to automate and/or do this for the rest of the cells in row 2? Note there's over 50 columns to do... any help appreciated peeps. Thank you.

 

[Yella Fella]

I believe you may be able to do this with Power Query.

Data > Get Data > From File

Split a column of text (Power Query) - Microsoft Support

Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. Using the Query Editor, a column of text can be split into multiple columns, either by: delimiter, such as a comma or using a number of characters.

support.microsoft.com

Transpose a table - Power Query

Describes how you can transpose a table in Power Query by rotating it 90 degrees using the transpose option.

learn.microsoft.com

I think you should then be able to apply this to all relevant columns.

If done regularly, I believe you can save the Query for reuse.

Almost... Power Query can do the first column expanded into rows... but it copies cell B2 into multiple row cells of the same user data. Same for C2 onwards. If applying the same method to column B, it multiples this which isn't good. A lot more work...

The issue is that everytime this data is exported, it has to be repeated per column manually somewhat with a transpose/copy/pasting.

The example above is small, the reality is, the data spans over 50 columns and each cell has over 1000 users separated by commas.