# repeated transpose from rows to columns with unequal groups

=?Utf-8?B?a3JheW1vbmQ=?=
Guest
Posts: n/a

 17th Dec 2004
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
Guest
Posts: n/a

 17th Dec 2004
One way:

1. Create a unique list of dates and parameters.

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

Debra Dalgleish
Guest
Posts: n/a

 17th Dec 2004
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

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

=?Utf-8?B?a3JheW1vbmQ=?=
Guest
Posts: n/a

 20th Dec 2004
Thank you, Jason. That worked perfectly.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Howard Microsoft Excel Programming 2 24th Dec 2012 10:04 AM Jim Microsoft Word Document Management 5 24th Jul 2012 01:16 PM hn7155 Microsoft Excel Worksheet Functions 7 13th Feb 2009 12:50 AM ph4nu Microsoft Excel Worksheet Functions 2 21st Aug 2008 05:14 PM =?Utf-8?B?T25yZXk=?= Microsoft Excel Misc 2 24th Mar 2005 02:45 PM

Features