Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple rows to one column
Thanks All! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transpose multiple rows into one column | Excel Discussion (Misc queries) | |||
combine multiple rows in one column | Excel Worksheet Functions | |||
Transposing One Column to Multiple Rows | Excel Worksheet Functions | |||
Way to put multiple rows into the same column. | Excel Discussion (Misc queries) | |||
Problem when trying to convert one column with multiple rows to one row with multiple column | Excel Programming |