This answer uses the data from How to convert row format data
into column format?, which was closed as a duplicate of this question.
So, this answer does answer this question, with trivial modifications
(i.e., ignore the parts of this answer that talk about data labels.
First of all, I’ll assume that your data are what you appear to be saying:
Create a new sheet
and enter column headings that correspond to your three data labels:
(You can do this by copying cells
Sheet1 and transposing them,
but, for just three, it’s probably easier just to type them in manually.)
Now, note that,
every time you move one cell (i.e., one column) to the right on
that corresponds to moving down one row on
And every time you move one row down on
that corresponds to moving down three rows on
So, guess that
=ROW()*3 + COLUMN() might be a useful formula,
and enter that into cells
Note that the values progress in the way that we want them to.
But we want
Sheet2!A2 to get its value from
so let’s subtract 7 and use the result as an offset.
=OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0)
which is the data that you want, but with the data labels still attached.
So, to fix that, change the formula to
=SUBSTITUTE(OFFSET(Sheet1!$A$1, ROW()*3+COLUMN()-7, 0), A$1 & ": ", "")
to strip the label (from row 1 off the current column) from the cell data:
and then you can copy and paste values.