Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike
 
Posts: n/a
Default Quasi Transpose / Stacking Columns

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

  #2   Report Post  
JulieD
 
Posts: n/a
Default

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



  #3   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #4   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #5   Report Post  
Alan Beban
 
Posts: n/a
Default

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



  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #7   Report Post  
Alan Beban
 
Posts: n/a
Default

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.

  #8   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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?


  #9   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #10   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.



  #11   Report Post  
Alan Beban
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns foofoo Excel Discussion (Misc queries) 1 April 2nd 05 12:02 AM
transpose a column into many rows GMed Excel Discussion (Misc queries) 1 January 21st 05 08:15 PM
Columns in Excel will not allow user to click in them Kim Excel Discussion (Misc queries) 1 December 28th 04 07:37 PM
how do I transpose columns and rows jnix Excel Discussion (Misc queries) 10 December 22nd 04 02:44 PM
repeated transpose from rows to columns with unequal groups kraymond Excel Discussion (Misc queries) 3 December 20th 04 03:39 PM


All times are GMT +1. The time now is 02:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"