Turn multiple rows into a single column

**mckaa302** · 05-26-2012, 05:38 PM

Yes. Here is a sample excel sheet.

Row 1 is composed of the variables.
Rows 2-5 (Column A) are four different people and then Column B-AC are the scores.

Right now I just did copy/paste special+transpose but to do that 750 times would take hours.

Sample Excel Sheet.xlsx

Thanks for any help you can provide!

**Ron Coderre** · 05-26-2012, 05:52 PM

Using your posted workbook...

These regular formulas will populate the source data in the destination range in the order you want:

A11: =INDEX($A$2:$A$5,CEILING(ROWS($10:10)/(COLUMNS($A$1:$AC$1)-1),1))
B11: =INDEX($B$1:$AC$1,MOD(ROWS($10:10)-1,COLUMNS($A$1:$AC$1)-1)+1)
C11: =INDEX($B$2:$AC$5,CEILING(ROWS($10:10)/(COLUMNS($A$1:$AC$1)-1),1),MOD(ROWS($10:10)-1,COLUMNS($A$1:$AC$1)-1)+1)

Copy those formulas down as far as you need.

Is that something you can work with?