Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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$50 00&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 . |
#3
![]() |
|||
|
|||
![]()
You could create a pivot table from the data. There are instructions in
Excel's Help, and he 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 |
#4
![]() |
|||
|
|||
![]()
Thank you, Jason. That worked perfectly.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
interchange columns with rows | Excel Discussion (Misc queries) | |||
Making Rows into Columns | Excel Discussion (Misc queries) |