PC Review


Reply
Thread Tools Rate Thread

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

 
Reply With Quote
 
 
 
 
Jason Morin
Guest
Posts: n/a
 
      17th Dec 2004
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
>
>.
>

 
Reply With Quote
 
 
 
 
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
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

 
Reply With Quote
 
=?Utf-8?B?a3JheW1vbmQ=?=
Guest
Posts: n/a
 
      20th Dec 2004
Thank you, Jason. That worked perfectly.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Transpose columns to rows in groups Howard Microsoft Excel Programming 2 24th Dec 2012 10:04 AM
Two-column page with unequal columns. Jim Microsoft Word Document Management 5 24th Jul 2012 01:16 PM
Transpose columns to rows using first columns repeated. hn7155 Microsoft Excel Worksheet Functions 7 13th Feb 2009 12:50 AM
Equally combining two unequal groups ph4nu Microsoft Excel Worksheet Functions 2 21st Aug 2008 05:14 PM
concatenating with unequal columns =?Utf-8?B?T25yZXk=?= Microsoft Excel Misc 2 24th Mar 2005 02:45 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:28 AM.