I have a table of data. Note that the number of rows per index can vary and the total number of unique index values will vary and not be named TestX:

Index Concat
Test1 DataValue1
Test1 DataValue2
Test1 DataValue3
Test1 DataValue4
Test1 DataValue5
Test1 DataValue6
Test1 DataValue7
Test1 DataValue8
Test1 DataValue9
Test2 DataValue10
Test2 DataValue11
Test2 DataValue12
Test2 DataValue13


Test2 DataValue14
Test2 DataValue15
Test2 DataValue16
Test2 DataValue17
Test2 DataValue18
Test2 DataValue19
Test3 DataValue20
Test3 DataValue21
Test3 DataValue22
Test3 DataValue23
Test3 DataValue24
Test3 DataValue25
Test3 DataValue26
Test3 DataValue27
Test3 DataValue28
Test3 DataValue29


I would like to transpose the data so each index item shows the related data on one row:


Capture1 hosted at ImgBB — ImgBB

Ultimately, I would like to filter it down to unique indexes, but I can do that myself:


Capture2 hosted at ImgBB — ImgBB

My question is can I do at least the column to row (second step/picture) conversion with a formula?



Thanks for any help you can provide.