Forum Discussion
Transpose Multiple Rows
Hi, I am trying to transpose a large amount of data. My problem is below. I am trying to transpose columns G-J...so I do the following {=transpose(G10:J10)} which works exactly how I want it to...
Dragging formulas is just the cheap and nasty (though mostly effective) way used by spreadsheets to make a formula scan the elements of an array or list. To reduce a crosstab to a list using formulas you will need an index column {1;2;...n} for the output range. Calling the index 'k' the values can be looked up from the source 'data' using the array formula
= INDEX( data, 1 + QUOTIENT( k-1, 4 ), 1 + MOD( k-1, 4 ) )