Transpose copy: Second Part

[Mika]

Hi,

4 months after the solution proposed by Herbert Seidenberg is time to
extend the capabilities of my spreadsheet...

here is the original post and the solution.


"I have a table in columns A trough E . The data in columns C and D,
I need to repeat and copy in a sigle column(column p for instance) in
this order:


d1
d1
c1
c1
d2
d2
c2
c2
..
..
..
I was trying with an expresion like: =INDEX($D:$D,(ROW())) for and easy



copy but can not figure out a proper formula (I need formulas in the
cells not values)... any help ?


Reply


From: Herbert Seidenberg
Enter this formula and copy down:
="^="&CHAR(67+IF(MOD(ROW()-1,4)<2,1,0))&FLOOR((ROW()-1)/4,1)+1
Paste Special > Value
Then Find/Replace the caret character

'''' Ok, now I want to find the min and max in a certain variable
interval. What I need is:
(I$1 has the size of the interval, let´s say 3.)

MIN(D1ffset($D1,I$1-1,0)) first cell
MIN(D1ffset($D1,I$1-1,0)) second cell
MIN(C1ffset($C1,I$1-1,0)) etc
MIN(C1ffset($D1,I$1-1,0))

the tricky part is that I$1 is not 1, then the rows of the cells has to
change (keeping 3 as example)

MIN(D4ffset($D4,I$1-1,0))
MIN(D4ffset($D4,I$1-1,0))

etc, so now that row number, 4 in the example, can not be hardcoded
but a variable...

Any help ?
Thanks for your time

Mika

 

[Paul Lautman]

Hi,

4 months after the solution proposed by Herbert Seidenberg is time to
extend the capabilities of my spreadsheet...

here is the original post and the solution.


"I have a table in columns A trough E . The data in columns C and D,
I need to repeat and copy in a sigle column(column p for instance) in
this order:


d1
d1
c1
c1
d2
d2
c2
c2

Try:

=OFFSET($C$1,INT((ROW()-1)/4),1-MOD(INT((ROW()-1)/2),2))