Appreciate any tips on doing the following matrix change:

Original Matrix
Date Series 1 Series 2 Series 3
1/1/2005 23 45 99
1/2/2005 24 46 100
1/3/2005 20 40 101
1/4/2005 22 40 99
1/5/2005 25 46 98

New Matrix
Date Series Data
1/1/2005 Series 1 23
1/2/2005 Series 1 24
1/3/2005 Series 1 20
1/4/2005 Series 1 22
1/5/2005 Series 1 25
1/1/2005 Series 2 45
1/2/2005 Series 2 46
1/3/2005 Series 2 40
1/4/2005 Series 2 40
1/5/2005 Series 2 46
1/1/2005 Series 3 99
1/2/2005 Series 3 100
1/3/2005 Series 3 101
1/4/2005 Series 3 99
1/5/2005 Series 3 98

Thanks in advance. Mike

Hi Mike

check out
http://j-walk.com/ss/excel/usertips/tip068.htm

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Mike" wrote in message
...
Appreciate any tips on doing the following matrix change:

Original Matrix
Date Series 1 Series 2 Series 3
1/1/2005 23 45 99
1/2/2005 24 46 100
1/3/2005 20 40 101
1/4/2005 22 40 99
1/5/2005 25 46 98

New Matrix
Date Series Data
1/1/2005 Series 1 23
1/2/2005 Series 1 24
1/3/2005 Series 1 20
1/4/2005 Series 1 22
1/5/2005 Series 1 25
1/1/2005 Series 2 45
1/2/2005 Series 2 46
1/3/2005 Series 2 40
1/4/2005 Series 2 40
1/5/2005 Series 2 46
1/1/2005 Series 3 99
1/2/2005 Series 3 100
1/3/2005 Series 3 101
1/4/2005 Series 3 99
1/5/2005 Series 3 98

Thanks in advance. Mike

Mike wrote:
Appreciate any tips on doing the following matrix change:

Original Matrix
Date Series 1 Series 2 Series 3
1/1/2005 23 45 99
1/2/2005 24 46 100
1/3/2005 20 40 101
1/4/2005 22 40 99
1/5/2005 25 46 98

New Matrix
Date Series Data
1/1/2005 Series 1 23
1/2/2005 Series 1 24
1/3/2005 Series 1 20
1/4/2005 Series 1 22
1/5/2005 Series 1 25
1/1/2005 Series 2 45
1/2/2005 Series 2 46
1/3/2005 Series 2 40
1/4/2005 Series 2 40
1/5/2005 Series 2 46
1/1/2005 Series 3 99
1/2/2005 Series 3 100
1/3/2005 Series 3 101
1/4/2005 Series 3 99
1/5/2005 Series 3 98

Thanks in advance. Mike


If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then
assuming your data, including headings, begins in Cell A1, enter in F1,
G1 and H1 Date, Series, Data, respectively, and then enter in F2, G2,
and H2 the following formulas and fill down as far as required:

=OFFSET($A$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1),0)

="Series "&INT((ROW(A1)-1)/(COUNTA(A:A)-1))+1

=INDEX(ArrayReshape(B$2:D$6,3*(COUNTA(A:A)-1),1,TRUE),ROW(A1))

Alan Beban

Alan Beban wrote:
Mike wrote:

Appreciate any tips on doing the following matrix change:

Original Matrix
Date Series 1 Series 2 Series 3
1/1/2005 23 45 99
1/2/2005 24 46 100
1/3/2005 20 40 101
1/4/2005 22 40 99
1/5/2005 25 46 98

New Matrix
Date Series Data
1/1/2005 Series 1 23
1/2/2005 Series 1 24
1/3/2005 Series 1 20
1/4/2005 Series 1 22
1/5/2005 Series 1 25
1/1/2005 Series 2 45
1/2/2005 Series 2 46
1/3/2005 Series 2 40
1/4/2005 Series 2 40
1/5/2005 Series 2 46
1/1/2005 Series 3 99
1/2/2005 Series 3 100
1/3/2005 Series 3 101
1/4/2005 Series 3 99
1/5/2005 Series 3 98

Thanks in advance. Mike


If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then
assuming your data, including headings, begins in Cell A1, enter in F1,
G1 and H1 Date, Series, Data, respectively, and then enter in F2, G2,
and H2 the following formulas and fill down as far as required:

=OFFSET($A$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1),0)

="Series "&INT((ROW(A1)-1)/(COUNTA(A:A)-1))+1

=INDEX(ArrayReshape(B$2:D$6,3*(COUNTA(A:A)-1),1,TRUE),ROW(A1))

Alan Beban

One can skip the functions from the Web site and use the following 3rd
fuormula in H2 instead:

=OFFSET($B$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1),INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

Alan Beban

The second formula I previously provided was hard coded to the extent
that it assumed column headings in Cells B1:D1 in the form of Series 1,
Series 2, Series 3; The second formula in the following set of 3
formulas for F2, G2 and H2, respectively, does not have this limitation;
when copied down it simply retuns the headings from B1:D1, whatever they
a

=OFFSET($A$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1),0)

=OFFSET($B$1,0,INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

=OFFSET($B$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1),INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

Alan Beban

Alan Beban wrote:
Alan Beban wrote:

Mike wrote:

Appreciate any tips on doing the following matrix change:

Original Matrix
Date Series 1 Series 2 Series 3
1/1/2005 23 45 99
1/2/2005 24 46 100
1/3/2005 20 40 101
1/4/2005 22 40 99
1/5/2005 25 46 98

New Matrix
Date Series Data
1/1/2005 Series 1 23
1/2/2005 Series 1 24
1/3/2005 Series 1 20
1/4/2005 Series 1 22
1/5/2005 Series 1 25
1/1/2005 Series 2 45
1/2/2005 Series 2 46
1/3/2005 Series 2 40
1/4/2005 Series 2 40
1/5/2005 Series 2 46
1/1/2005 Series 3 99
1/2/2005 Series 3 100
1/3/2005 Series 3 101
1/4/2005 Series 3 99
1/5/2005 Series 3 98

Thanks in advance. Mike

Alan Beban wrote...
The second formula I previously provided was hard coded to the extent
that it assumed column headings in Cells B1:D1 in the form of Series
1,
Series 2, Series 3; The second formula in the following set of 3
formulas for F2, G2 and H2, respectively, does not have this
limitation;
when copied down it simply retuns the headings from B1:D1, whatever
they
a

=OFFSET($A$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1),0)

=OFFSET($B$1,0,INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

=OFFSET($B$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))
*(COUNTA(A:A)-1),INT((ROW(A1)-1)/(COUNTA(A:A)-1)))
....

Your formulas also assume that there's nothing below the data in column
A.
That's unwise. Better to give the original data including the top row
containing the series names a defined name like Orig, then use formulas
like the following (assumes top-left result cell is F2).

F2:
=OFFSET(Orig,MOD(ROW()-ROW($F$2),ROWS(Orig)-1)+1,0,1,1)

G2:
=OFFSET(Orig,0,INT((ROW()-ROW($F$2))/(ROWS(Orig)-1))+1,1,1)

H2:
=OFFSET(Orig,MOD(ROW()-ROW($F$2),ROWS(Orig)-1)+1,
INT((ROW()-ROW($F$2))/(ROWS(Orig)-1))+1,1,1)

Then fill F2:H2 down as far as needed. Another advantage to these
formulas
is that they only refer to cells in Orig and cells in the result range
(actually just to F2), which means you can move the result range
anywhere
and the formulas will automatically adjust correctly. Alan's formulas
only
work if the top row of the result range is row 2 in the worksheet.

Harlan Grove wrote:
Alan Beban wrote...

The second formula I previously provided was hard coded to the extent
that it assumed column headings in Cells B1:D1 in the form of Series

1,

Series 2, Series 3; The second formula in the following set of 3
formulas for F2, G2 and H2, respectively, does not have this

limitation;

when copied down it simply retuns the headings from B1:D1, whatever

they

a

=OFFSET($A$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1),0)

=OFFSET($B$1,0,INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

=OFFSET($B$2,ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))
*(COUNTA(A:A)-1),INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

...

Your formulas also assume that there's nothing below the data in column
A.

That's true. The formulas I suggested work if, as I said, the data
including the headings begins in Cell A1, and, as Harlan Grove pointed
out, if there is no data below the data being worked with, at least in
Column A. And they can simply be copied down further as additional data
is added in A:D.

If those constraints are unacceptable, then of course other formulas
would be more suitable. Perhaps Harlan Grove's, if one is willing to
rename the working range each time additions are made, or otherwise
adapt the formulas to accommodate such additions.

Alan Beban

That's unwise. Better to give the original data including the top row
containing the series names a defined name like Orig, then use formulas
like the following (assumes top-left result cell is F2).

F2:
=OFFSET(Orig,MOD(ROW()-ROW($F$2),ROWS(Orig)-1)+1,0,1,1)

G2:
=OFFSET(Orig,0,INT((ROW()-ROW($F$2))/(ROWS(Orig)-1))+1,1,1)

H2:
=OFFSET(Orig,MOD(ROW()-ROW($F$2),ROWS(Orig)-1)+1,
INT((ROW()-ROW($F$2))/(ROWS(Orig)-1))+1,1,1)

Then fill F2:H2 down as far as needed. Another advantage to these
formulas
is that they only refer to cells in Orig and cells in the result range
(actually just to F2), which means you can move the result range
anywhere
and the formulas will automatically adjust correctly. Alan's formulas
only
work if the top row of the result range is row 2 in the worksheet.

"Alan Beban" wrote...
....
. . . if one is willing to
rename the working range each time additions are made, or otherwise
adapt the formulas to accommodate such additions.
....

Not comfortable showing how to define dynamic ranges? Well, here's one way.

If the data range spans columns A through D beginning in row 2 and spanning
all rows in which column A is nonblank moving down from cell A2. Define Orig
referring to

=OFFSET(WorksheetNameHere!$A$2:$D$2,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$65536 ),0)-1,4)


But what really irked was your bizarre use of the expression

ROW(A1)-1-INT((ROW(A1)-1)/(COUNTA(A:A)-1))*(COUNTA(A:A)-1)

rather than the simpler

MOD(ROW(A1)-1,COUNTA(A:A)-1)

Yes, Excel's MOD function does have it problems, but not when remainders are
less than 65536. You got something against MOD?

Harlan Grove wrote:
"Alan Beban" wrote...
...

. . . if one is willing to
rename the working range each time additions are made, or otherwise
adapt the formulas to accommodate such additions.

...

Not comfortable showing how to define dynamic ranges? Well, here's one way.

If the data range spans columns A through D beginning in row 2 and spanning
all rows in which column A is nonblank moving down from cell A2. Define Orig
referring to

=OFFSET(WorksheetNameHere!$A$2:$D$2,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$65536 ),0)-1,4)


So your formulas don't work if the data range starts in Cell A1?

Alan Beban

Alan Beban wrote...
Harlan Grove wrote:
....
=OFFSET(WorksheetNameHere!$A$2:$D$2,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$655 36),0)-1,4)

So your formulas don't work if the data range starts in Cell A1?

It's subject to the usual restriction that derived arrays can't span
65536 rows. If you were starting in row 1 originally, then you might
have though about changing the formula to

=OFFSET(WorksheetNameHere!$A$1:$D$1,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$1:$A$65535 ),0)-1,4)

but it appears you created the defined name as given, then moved the
data range from A2:D# to A1:D(#-1). I'll admit that isn't general
enough, so if the data range could start in row 1 and span all 65536
rows, then the formula should be changed to

=OFFSET(WorksheetNameHere!$A$1:$D$1,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$65536 ),0),4)

If there were then no records in the range, only the column headings in
row 1, all the formulas would return #DIV/0! I don't consider that a
drawback, but if you do, you could take the uncharacteristic step of
showing how to trap it rather than playing(?) dumb.

Harlan Grove wrote:
Alan Beban wrote...

Harlan Grove wrote:

...

=OFFSET(WorksheetNameHere!$A$2:$D$2,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$65 536),0)-1,4)

So your formulas don't work if the data range starts in Cell A1?


It's subject to the usual restriction that derived arrays can't span
65536 rows. If you were starting in row 1 originally, then you might
have though about changing the formula to

=OFFSET(WorksheetNameHere!$A$1:$D$1,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$1:$A$65535 ),0)-1,4)

but it appears you created the defined name as given, then moved the
data range from A2:D# to A1:D(#-1). I'll admit that isn't general
enough, so if the data range could start in row 1 and span all 65536
rows, then the formula should be changed to

=OFFSET(WorksheetNameHere!$A$1:$D$1,0,0,
MATCH(TRUE,ISBLANK(WorksheetNameHere!$A$2:$A$65536 ),0),4)

If there were then no records in the range, only the column headings in
row 1, all the formulas would return #DIV/0! I don't consider that a
drawback, but if you do, you could take the uncharacteristic step of
showing how to trap it rather than playing(?) dumb.

Hey, I took my shot at providing something useful for the OP and any
interested users. You didn't like mine and suggested a different
approach and that's fine--all the better for the users. But I'm neither
being nor playing dumb; it's your baby and I'm inclined to let *you*
think through and clean up your previously omitted details so that an
interested user can readily apply it.

Alan Beban