[Solved] Transposing GROUPS of cells

[meldor]

hello i am currently trying to transpose groups of cells to another worksheet. Example here : http://stackoverflow.com/questions/1728 ... ffice-calc . how do i achieve this?how can i delimit groups of cells?
Thank you for your patience.

[ken johnson]

Maybe the INDEX function with row parameter determined by the MOD function and column parameter determined by the INT function...

Code: Select all

=IF(INDEX($Sheet1.$A$1:$AMJ$65536;MOD(ROW(A1)-ROW(A$1);5)+1;INT((ROW(A1)-ROW(A$1))/5)*3+COLUMN(A1)-COLUMN($A1)+1)="";"";INDEX($Sheet1.$A$1:$AMJ$65536;MOD(ROW(A1)-ROW(A$1);5)+1;INT((ROW(A1)-ROW(A$1))/5)*3+COLUMN(A1)-COLUMN($A1)+1))

3 Row 2 Column groups into 2 Columns.ods

(8.66 KiB) Downloaded 145 times

Ken Johnson

[meldor]

Thanks it seems to work just fine... One more thing: assuming all rows for each clump are non-void how could i do this for variable rows?

[ken johnson]

If you mean that each two column group could be any number of rows deep instead of each being three rows deep then I'm fairly sure you will need to use a Helper column that the INDEX function's row and column parameters can use to determine the correct values to use.
See if the formulae on Sheet 4 acting on the data in Sheet3 are behaving the way you are wanting...

Variable Row 2 Column groups into 2 Columns.ods

(11.8 KiB) Downloaded 149 times

Ken Johnson

[meldor]

You are quite the ruler thanks.