Multiple rows to one column
 

Each month I get hourly temperature data in this format:
Row1: 05/01/04 hour1, hour2...
Row2: 05/02/04 hour1, hour2...
I need to transpose the columns(hours1 through24)so they
are listed into one columnthis must be done for each day
of the month. It should look like:

05/01/04 hour1
05/01/04 hour2
05/02/04 hour3...

05/31/04 hour24
For the month of May, I would have 744 rows and one
column. I would like to right code that would do this to
selected data. I am using Excel 2002. Thanks!

Multiple rows to one column
 

Hi Trip

Assuming your current data is within columns A to Y and columns AA and AB do
not contain any data, try...

Sub test()
Dim r As Range, rr As Range
Dim c As Range, rng As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
..Range("AA:AB").ClearContents
Set r = .Range(.Range("A1"), _
..Range("A" & Rows.Count).End(xlUp))
For Each c In r
Set rr = .Range(c.Offset(0, 1), c.Offset(0, 24))
Set rng = .Range("AA" & Rows.Count).End(xlUp).Offset(1, 0)
Set rng = .Range(rng, rng.Offset(23, 0))
rng.Formula = c.Value2
rng.Offset(0, 1) = Application.Transpose(rr.Value)
Next c
'Amend next line as appropriate
..Columns("AA:AA").NumberFormat = "dd mmm yy"
..Range("AA1:AB1").Delete Shift:=xlUp
Application.ScreenUpdating = True
End With
End Sub

--
XL2002
Regards

William



"Trip Levert" wrote in message
...
| Each month I get hourly temperature data in this format:
| Row1: 05/01/04 hour1, hour2...
| Row2: 05/02/04 hour1, hour2...
| I need to transpose the columns(hours1 through24)so they
| are listed into one columnthis must be done for each day
| of the month. It should look like:
|
| 05/01/04 hour1
| 05/01/04 hour2
| 05/02/04 hour3...
|
| 05/31/04 hour24
| For the month of May, I would have 744 rows and one
| column. I would like to right code that would do this to
| selected data. I am using Excel 2002. Thanks!
|

Multiple rows to one column
 

One way:-

http://j-walk.com/ss/excel/usertips/tip068.htm


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Trip Levert" wrote in message
...
Each month I get hourly temperature data in this format:
Row1: 05/01/04 hour1, hour2...
Row2: 05/02/04 hour1, hour2...
I need to transpose the columns(hours1 through24)so they
are listed into one columnthis must be done for each day
of the month. It should look like:

05/01/04 hour1
05/01/04 hour2
05/02/04 hour3...

05/31/04 hour24
For the month of May, I would have 744 rows and one
column. I would like to right code that would do this to
selected data. I am using Excel 2002. Thanks!



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004

Multiple rows to one column
 

Hi William, long time no speak - Hope things are all OK in your neck of the
woods :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004

Multiple rows to one column
 

Hi Ken

Have you read the book "CHAOS" :-)

Hope you're well

--
XL2002
Regards

William



"Ken Wright" wrote in message
...
| Hi William, long time no speak - Hope things are all OK in your neck of
the
| woods :-)
|
| --
| Regards
| Ken....................... Microsoft MVP - Excel
| Sys Spec - Win XP Pro / XL 97/00/02/03
|
| --------------------------------------------------------------------------
--
| It's easier to beg forgiveness than ask permission :-)
| --------------------------------------------------------------------------
--
|
| <snip
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
|
|

Multiple rows to one column
 

LOL - Read it???? Right now I think I'm living it <g

3 hectic proposals on the go / Either moving house or serious extension work
being considered, so lot sof house hunting going on / and on top of that chasing
a new job that might see me up in Hertford for most of the week if I choose to
go that route. Long way to travel though when you have been used to a 4 minute
drive to/from work for the last 8 years. Huge opportunity, but big big change
in Work/Life balance, which right now is pretty good, so Jury's still out on
that one, especially with having kids.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"William" wrote in message
...
Hi Ken

Have you read the book "CHAOS" :-)

Hope you're well

--
XL2002
Regards

William



"Ken Wright" wrote in message
...
| Hi William, long time no speak - Hope things are all OK in your neck of
the
| woods :-)
|
| --
| Regards
| Ken....................... Microsoft MVP - Excel
| Sys Spec - Win XP Pro / XL 97/00/02/03
|
| --------------------------------------------------------------------------
--
| It's easier to beg forgiveness than ask permission :-)
| --------------------------------------------------------------------------
--
|
| <snip
|
|
| ---
| Outgoing mail is certified Virus Free.
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
|
|




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004

Multiple rows to one column
 

Thanks All!