Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
"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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
transpose a column into many rows | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
how do I transpose columns and rows | Excel Discussion (Misc queries) | |||
repeated transpose from rows to columns with unequal groups | Excel Discussion (Misc queries) |