Transpose Multiple Rows Into Single, Concatenated Column

[warrevar]

I want to transpose multiple items out of multiple rows into a single, concatenated column.  For instance, starting with...

      CA  CB  CC  CD

R1   X    1     2    3

R2   Y    4     5    6

Would change into...

      CE

R1  X-1

R2  X-2

R3  X-3

R4  Y-4

R5  Y-5

R6  Y-6

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).  I've seen a few other, similar formulas:

• https://www.extendoffice.com/documents/excel/3360-excel-transpose-every-5-rows.html

Any suggestions, Microsoft Community?

Thanks!

[#2]

how many rows does your dataset have

[#3]

No more than 200 rows and 35 columns.

[#4]

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)&[index column] after you close and load