Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
kraymond
 
Posts: n/a
Default repeated transpose from rows to columns with unequal groups

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   Report Post  
Jason Morin
 
Posts: n/a
Default

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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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   Report Post  
kraymond
 
Posts: n/a
Default

Thank you, Jason. That worked perfectly.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
interchange columns with rows Herman Excel Discussion (Misc queries) 2 December 8th 04 06:40 PM
Making Rows into Columns Tony Williams Excel Discussion (Misc queries) 1 December 2nd 04 04:47 PM


All times are GMT +1. The time now is 09:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"