repeated transpose from rows to columns with unequal groups

Discussion in 'Microsoft Excel Misc' started by Guest, Dec 17, 2004.

  1. Guest

    Guest Guest

    I have thousands of rows of data in the following format (in Excel 2000):

    Date Parameter Value
    3/10/79 Temp 22
    3/10/79 Oxygen 2.5
    4/1/80 Temp 25
    2/24/81 Temp 23
    2/24/81 Oxygen 1.0
    2/24/81 pH 7.0

    I want to change it to:

    Date Temp Oxygen pH
    3/10/79 22 2.5
    4/1/80 25
    2/24/81 23 1.0 7.0

    I could (and have) manually copy and transpose the values for each date.
    However, as in my example, each date may have a different set of parameters.
    Therefore, the resulting rows would not have the same number or even name for
    resulting columns. This makes the transposition very time-consuming, as I
    must transpose and then move the data into the correct columns.

    I have seen formulas that will automate row-to-column conversion for set of
    values that repeat (like every group of four rows in a column converts to a
    row). The suggestions were like the formula below:
    =OFFSET($A$1,(ROW(1:1)-1)*4,0)

    Is there any way to modify this for my situation? Each group that needs to
    be transposed has a unique date.

    Thank you
     
    Guest, Dec 17, 2004
    #1
    1. Advertisements

  2. Guest

    Jason Morin Guest

    One way:

    1. Create a unique list of dates and parameters.
    http://www.contextures.com/xladvfilter01.html#FilterUR

    2. Place the unique list of dates in a new sheet,
    starting in A2. Place the unique list of parameters in B1
    horizontally across the top.

    3. Place this in B2, press ctrl/shift/enter, and fill in
    across and down.

    =INDEX(ws!$C$1:$C$5000,MATCH(B$1&$A2,ws!$B$1:$B$5000&ws!
    $A$1:$A$5000,0))

    This assumes the source worksheet is named "ws".

    4. You can get rid of the #N/A by copying and paste
    special > values over the formulas, and use Edit >
    Replace.

    HTH
    Jason
    Atlanta, GA



    >-----Original Message-----
    >I have thousands of rows of data in the following format

    (in Excel 2000):
    >
    >Date Parameter Value
    >3/10/79 Temp 22
    >3/10/79 Oxygen 2.5
    >4/1/80 Temp 25
    >2/24/81 Temp 23
    >2/24/81 Oxygen 1.0
    >2/24/81 pH 7.0
    >
    >I want to change it to:
    >
    >Date Temp Oxygen pH
    >3/10/79 22 2.5
    >4/1/80 25
    >2/24/81 23 1.0 7.0
    >
    >I could (and have) manually copy and transpose the

    values for each date.
    >However, as in my example, each date may have a

    different set of parameters.
    >Therefore, the resulting rows would not have the same

    number or even name for
    >resulting columns. This makes the transposition very

    time-consuming, as I
    >must transpose and then move the data into the correct

    columns.
    >
    >I have seen formulas that will automate row-to-column

    conversion for set of
    >values that repeat (like every group of four rows in a

    column converts to a
    >row). The suggestions were like the formula below:
    >=OFFSET($A$1,(ROW(1:1)-1)*4,0)
    >
    >Is there any way to modify this for my situation? Each

    group that needs to
    >be transposed has a unique date.
    >
    >Thank you
    >
    >.
    >
     
    Jason Morin, Dec 17, 2004
    #2
    1. Advertisements

  3. You could create a pivot table from the data. There are instructions in
    Excel's Help, and here:

    http://peltiertech.com/Excel/Pivots/pivotstart.htm

    If you use a dynamic range for the source, it will expand automatically
    as new rows are added.

    kraymond wrote:
    > I have thousands of rows of data in the following format (in Excel 2000):
    >
    > Date Parameter Value
    > 3/10/79 Temp 22
    > 3/10/79 Oxygen 2.5
    > 4/1/80 Temp 25
    > 2/24/81 Temp 23
    > 2/24/81 Oxygen 1.0
    > 2/24/81 pH 7.0
    >
    > I want to change it to:
    >
    > Date Temp Oxygen pH
    > 3/10/79 22 2.5
    > 4/1/80 25
    > 2/24/81 23 1.0 7.0
    >
    > I could (and have) manually copy and transpose the values for each date.
    > However, as in my example, each date may have a different set of parameters.
    > Therefore, the resulting rows would not have the same number or even name for
    > resulting columns. This makes the transposition very time-consuming, as I
    > must transpose and then move the data into the correct columns.
    >
    > I have seen formulas that will automate row-to-column conversion for set of
    > values that repeat (like every group of four rows in a column converts to a
    > row). The suggestions were like the formula below:
    > =OFFSET($A$1,(ROW(1:1)-1)*4,0)
    >
    > Is there any way to modify this for my situation? Each group that needs to
    > be transposed has a unique date.
    >
    > Thank you
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html
     
    Debra Dalgleish, Dec 17, 2004
    #3
  4. Guest

    Guest Guest

    Thank you, Jason. That worked perfectly.
     
    Guest, Dec 20, 2004
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bruce

    Rows columns transpose & link

    Bruce, Aug 16, 2003, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    186
    Ken Wright
    Aug 16, 2003
  2. Ari
    Replies:
    2
    Views:
    141
    Gord Dibben
    Apr 19, 2004
  3. Guest

    concatenating with unequal columns

    Guest, Mar 23, 2005, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    179
    Guest
    Mar 24, 2005
  4. Guest
    Replies:
    1
    Views:
    362
    Guest
    Feb 13, 2006
  5. geanswerman

    repeated rows to single rows with many columns

    geanswerman, Jun 11, 2009, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    112
Loading...

Share This Page