Page 1 of 1
[Solved] Transposing GROUPS of cells
Posted: Tue Jun 25, 2013 2:15 am
by 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.
Re: Transposing GROUPS of cells
Posted: Tue Jun 25, 2013 4:20 am
by 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))
Ken Johnson
Re: Transposing GROUPS of cells
Posted: Tue Jun 25, 2013 12:03 pm
by 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?
Re: Transposing GROUPS of cells
Posted: Tue Jun 25, 2013 2:15 pm
by 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...
Ken Johnson
Re: Transposing GROUPS of cells
Posted: Wed Jun 26, 2013 1:48 pm
by meldor
You are quite the ruler thanks.