Convert multiple rows with different number of columns into a single row

[tornavida]

Hello everyone,

First of all, if this is a very simple issue I apologize for keeping the board busy but I couldn't find a way out of this very simple looking problem:

I have 4 rows each of which has different numbers of columns containing data. I want to convert them into a single row, without merging the cells. So I want the output to be something like:

So the formula basically has to add the first row, when there is no more data left on that column, it should start from the beginning of the next row and keep adding the same way with all the rows. I tried to make it work with OFFSET and INDEX but couldn't get out of the mess. I would be glad if you could show me how to do this. A solution that would work with more rows would be very welcome.

Thank you!

 

[alansidman]

A power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

 

[Legacy 143009]

Paste this formula to B1 and pull to the right.

=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,$B$2:$G$4),",",REPT(" ",99)),(COLUMN(A$1:A1)-1)*99+1,99))

 

[Fluff]

Hi & welcome to MrExcel.
Another option in B7 dragged across

=FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,$B$2:$G$4)&"</m></k>","//m[string-length()>0]["&COLUMNS($B7:B7)&"]")

 

[tornavida]

Cheers everyone, that was super helpful and quick! After some little tweaks for my data set, any of them work perfectly. Thanks a lot!

 

[Legacy 143009]

Thanks for the feedback

 

[Fluff]

Glad we could help & thanks for the feedback.