Transpose Multiple Rows Into Single, Concatenated Column

Question

I want to transpose multiple items out of multiple rows into a single, concatenated column.&nbsp; For instance, starting with...&nbsp;&nbsp; &nbsp; &nbsp; CA&nbsp; CB&nbsp; CC&nbsp; CDR1&nbsp; &nbsp;X&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; 3R2&nbsp; &nbsp;Y&nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; 6&nbsp;Would change into...&nbsp; &nbsp; &nbsp; CER1&nbsp; X-1R2&nbsp; X-2R3&nbsp; X-3R4&nbsp; Y-4R5&nbsp; Y-5R6&nbsp; Y-6&nbsp;It has to be a formula rather than a script (i.e., the table must be easily copy/pasted and instantly updated without any intermediate steps).&nbsp; I've seen a few other, similar formulas:&nbsp;https://www.extendoffice.com/documents/excel/3360-excel-transpose-every-5-rows.htmlAny suggestions, Microsoft Community?&nbsp;Thanks!

yea_so · Answer

how many rows does your dataset have

warrevar · Answer

No more than 200 rows and 35 columns.

yea_so · Answer

have you tried power query and unpivoting other columns starting with columns CB then add an index column after unpivoting the other columns? I tried it but merging the R's with the index numbers yeilds the numbers before the letter so it looks like 1R instead of R1 so you'll have to do the left([column],1)&amp;[index column] after you close and load

Forum Discussion

Transpose Multiple Rows Into Single, Concatenated Column

3 Replies

Resources