Transpose copy: Second Part

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: =3DINDEX($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:
=3D"^=3D"&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=B4s say 3.)

MIN(D1:offset($D1,I$1-1,0)) first cell
MIN(D1:offset($D1,I$1-1,0)) second cell
MIN(C1:offset($C1,I$1-1,0)) etc
MIN(C1:offset($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(D4:offset($D4,I$1-1,0))
MIN(D4:offset($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

Mika wrote:
> 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))