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

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
>
>.
>

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

Thank you, Jason. That worked perfectly.