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.
What I need to do know is click and drag the formula down so that the next data I want to transpose it skips to {=transpose(G14:J14)} and so on when I really want it to go to {=transpose(G11:J11)} and so on.
Please Help!!
2 Replies
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 ) )
Don't really understand what range of cells you want to transpose. If, e.g. the range is G10:J20 you can simply select a destination cell and enter =TRANSPOSE(G10:J20). Just make sure that the resulting output does not overlap the initial range, because then you will get a #SPILL error.
