Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose and concatenate
My data:
Column names a patientID, date, reading1, reading2....reading12 row1: 918273, 20090901, 12.1, 5.6, ......9.4 row2: 918273, 20090902, 8.3, 3.8, ......1.8 rows keep going... I want the data to look like: patientID, date, reading 918273, 01-Sep-09, 00:00, 12.1 918273, 01-Sep-09, 02:00, 5.6 .. .. .. 918273, 01-Sep-09, 22:00, 9.4 918273, 02-Sep-09, 00:00, 8.3 918273, 02-Sep-09, 02:00, 3.8 .. .. .. 918273, 02-Sep-09, 22:00, 1.8 So what's happening is there are 12 readings per day, I want the readings to go down the page and add a column with the time so people know what time the reading was taken. And keep adding the days down the worksheet. Is that possible in excel? Thanking you. Diana |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose and concatenate
Do you always have 12 entries--or should the macro check?
Where do you get the time values? I don't see them in your sample "before" data. (Or do you just want an empty column so that you can fill them in manually???) Diana wrote: My data: Column names a patientID, date, reading1, reading2....reading12 row1: 918273, 20090901, 12.1, 5.6, ......9.4 row2: 918273, 20090902, 8.3, 3.8, ......1.8 rows keep going... I want the data to look like: patientID, date, reading 918273, 01-Sep-09, 00:00, 12.1 918273, 01-Sep-09, 02:00, 5.6 . . . 918273, 01-Sep-09, 22:00, 9.4 918273, 02-Sep-09, 00:00, 8.3 918273, 02-Sep-09, 02:00, 3.8 . . . 918273, 02-Sep-09, 22:00, 1.8 So what's happening is there are 12 readings per day, I want the readings to go down the page and add a column with the time so people know what time the reading was taken. And keep adding the days down the worksheet. Is that possible in excel? Thanking you. Diana -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose and concatenate
Hi Dave,
Yes, only 12 values, if there are anything after i can ignore. No there are no time values, the readings are taken every 2 hours, always midnight, then 2am, 4am etc.. If would be nice to do the lot through a macro and not have to do parts manually. Thanks dave. "Dave Peterson" wrote: Do you always have 12 entries--or should the macro check? Where do you get the time values? I don't see them in your sample "before" data. (Or do you just want an empty column so that you can fill them in manually???) Diana wrote: My data: Column names a patientID, date, reading1, reading2....reading12 row1: 918273, 20090901, 12.1, 5.6, ......9.4 row2: 918273, 20090902, 8.3, 3.8, ......1.8 rows keep going... I want the data to look like: patientID, date, reading 918273, 01-Sep-09, 00:00, 12.1 918273, 01-Sep-09, 02:00, 5.6 . . . 918273, 01-Sep-09, 22:00, 9.4 918273, 02-Sep-09, 00:00, 8.3 918273, 02-Sep-09, 02:00, 3.8 . . . 918273, 02-Sep-09, 22:00, 1.8 So what's happening is there are 12 readings per day, I want the readings to go down the page and add a column with the time so people know what time the reading was taken. And keep adding the days down the worksheet. Is that possible in excel? Thanking you. Diana -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose and concatenate
First, I've made a couple of assumptions.
I'm guessing that the "dates" in your data aren't really dates--I'm guessing that they're really just numbers (or text). (Distinguishing between dates formatted as yyyymmdd.) So I converted them to real dates. And the times is a problem. When you get to midnight (00:00:00) of the 1st, it looks like you want it to be the 24th hour of the day. The bad news is that when you enter that 00:00:00, it's a time that's before the first 23 entries. I don't think that's what your data represented. So instead of using 02:00:00, 04:00:00, ..., 00:00:00, I subtracted a second and used: 01:59:59, 03:59:59, ..., 23:59:59. (better for sorting/charting...) You can change the formatting of both these fields to something you like better. But I wanted to make sure that the dates/times came out ok, so I used an unambiguous number format. Anyway... Option Explicit Sub testme01() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim HowMany As Long Dim TotalEntries As Long Dim oRow As Long TotalEntries = 12 'how many there should be Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Resize(1, 4).Value _ = Array("PatientID", "Date", "Time", "Reading") With CurWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 2 For iRow = FirstRow To LastRow HowMany = Application.CountA(.Cells(iRow, "C") _ .Resize(1, TotalEntries)) If HowMany < TotalEntries Then MsgBox "Error on row: " & iRow _ & vbLf & "Stopping!" Exit Sub End If 'patient id NewWks.Cells(oRow, "A").Resize(TotalEntries, 1).Value _ = .Cells(iRow, "A").Value 'date (converted from string???) NewWks.Cells(oRow, "B").Resize(TotalEntries, 1).NumberFormat _ = "mmmm dd, yyyy" NewWks.Cells(oRow, "B").Resize(TotalEntries, 1).Value _ = DateSerial(Left(.Cells(iRow, "B").Value, 4), _ Mid(.Cells(iRow, "B").Value, 5, 2), _ Right(.Cells(iRow, "B").Value, 2)) 'time With NewWks.Cells(oRow, "C").Resize(TotalEntries, 1) .NumberFormat = "hh:mm:ss" .Formula = "=time((2*(ROW()-2))+1,59,59)" .Value = .Value End With 'reading NewWks.Cells(oRow, "D").Resize(TotalEntries, 1).Value _ = Application.Transpose(.Cells(iRow, "C") _ .Resize(1, TotalEntries)) oRow = oRow + TotalEntries Next iRow End With NewWks.UsedRange.Columns.AutoFit End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Diana wrote: Hi Dave, Yes, only 12 values, if there are anything after i can ignore. No there are no time values, the readings are taken every 2 hours, always midnight, then 2am, 4am etc.. If would be nice to do the lot through a macro and not have to do parts manually. Thanks dave. "Dave Peterson" wrote: Do you always have 12 entries--or should the macro check? Where do you get the time values? I don't see them in your sample "before" data. (Or do you just want an empty column so that you can fill them in manually???) Diana wrote: My data: Column names a patientID, date, reading1, reading2....reading12 row1: 918273, 20090901, 12.1, 5.6, ......9.4 row2: 918273, 20090902, 8.3, 3.8, ......1.8 rows keep going... I want the data to look like: patientID, date, reading 918273, 01-Sep-09, 00:00, 12.1 918273, 01-Sep-09, 02:00, 5.6 . . . 918273, 01-Sep-09, 22:00, 9.4 918273, 02-Sep-09, 00:00, 8.3 918273, 02-Sep-09, 02:00, 3.8 . . . 918273, 02-Sep-09, 22:00, 1.8 So what's happening is there are 12 readings per day, I want the readings to go down the page and add a column with the time so people know what time the reading was taken. And keep adding the days down the worksheet. Is that possible in excel? Thanking you. Diana -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to transpose and concatenate long list in one column? | Excel Worksheet Functions | |||
Transpose after CONCATENATE | Excel Worksheet Functions | |||
Help using Transpose | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |