transpose and concatenate

[Diana]

My data:

Column names are: 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]

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]

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]

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 here:
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.)