Repeat Cell Values

[tyler0803]

Hello all, 

I am trying to transpose data in a way that is more readable for a software I will be using. I have attached an image that contains what the current table looks like, and then the desired format underneath. If anything, I would love to figure out how to repeat every cell in the far left column 5 times. I know of a formula that puts all of the number data in the table in one column. Does anyone have any experience with this?

Thank you!

[#2]

@tyler0803 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

[#3]

@OliverScheurich 

Thank you! What if I wanted to expand the table by a few columns and rows? Would I have to change the code in the query?

[#4]

@tyler0803 

Formula version:

=DROP(
    REDUCE(
        "",
        letters,
        LAMBDA(a, v,
            LET(
                val, XLOOKUP(v, letters, data),
                SORT(VSTACK(a, HSTACK(labels, EXPAND(v, ROWS(val), , v), val)), {1, 2})
            )
        )
    ),
    1
)

For a large data set use the PowerQuery solution!

[#5]

@tyler0803 

The code in the query doesn't have to be changed. The number of rows and columns of the blue table is recognized by the code because the tables are dynamic. Data can be added to the blue table and after selecting refresh the green table returns the updated result.