Convert to new format

[BloodyBill]

This is hard to explain, and easier to show.
I have thousands of these time entries sitting in many ranges like the example on the left. The ranges are spread over about 12 sheets. They work great, except I am forced to convert to a new format on the right.
Can anyone suggest a way that is easier than a basic manual process? Thanks!

 

[mackc557]

Before trying to give an answer, I should ask a few questions first:

Will there ever be 2 or more in the same time slot for the same Thing?
Will they always be in ascending order?
Will the times given always be in increments of 5 minutes?

 

[BloodyBill]

Before trying to give an answer, I should ask a few questions first:

Will there ever be 2 or more in the same time slot for the same Thing?
Will they always be in ascending order?
Will the times given always be in increments of 5 minutes?

Good questions. Answers

1. There might be times with more than one Thing in a given time slot, like: 7:30 PM 1 1 1
   but I don't think there's ever going to be two of any given thing, like: 7:30 PM 2 1 1
   
   
2. Yes, the times should ascend in chronological order, often from morning to early evening.
   
   
3. The times will always be in increments of 5 minutes (no 11:32) but there may be gaps with no data (that I don't want). Notice the gap between 11:45 and12:10.
   

 

[BloodyBill]

It dawns on me that my example on the right is not correct. For instance, 9:40 AM should be one line that reads: 1 1 (blank)

Also, there are often Things 4, 5 and 6.

 

[offthelip]

try this:

 Sub test()
 Dim myrange As Range
 Lra = Cells(Rows.Count, "A").End(xlUp).Row
 Lrb = Cells(Rows.Count, "B").End(xlUp).Row
 Lrc = Cells(Rows.Count, "C").End(xlUp).Row
cola = Range(Cells(2, 1), Cells(Lra, 1))
colb = Range(Cells(2, 2), Cells(Lrb, 2))
colc = Range(Cells(2, 3), Cells(Lrc, 3))
hdrs = Range(Cells(1, 1), Cells(1, 3))
Range(Cells(1, 2), Cells(1, 4)) = hdrs
Range(Cells(1, 1), Cells(1, 1)) = "Time"
Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 4)) = ""
Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 1)).NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range(Cells(2, 2), Cells(Lra + Lrb + Lrc, 4)).NumberFormat = "0.000"

Range(Cells(2, 1), Cells(Lra, 1)) = cola
Range(Cells(2, 2), Cells(Lra, 2)) = 1
Range(Cells(Lra + 1, 3), Cells(Lra + Lrb - 1, 3)) = 1
Range(Cells(Lra + 1, 1), Cells(Lra + Lrb - 1, 1)) = colb
Range(Cells(Lra + Lrb + 1, 4), Cells(Lra + Lrb + Lrc - 1, 4)) = 1
Range(Cells(Lra + Lrb + 1, 1), Cells(Lra + Lrb + Lrc - 1, 1)) = colc
    Set myrange = Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 4))
    Set Sortkey = Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 1))
    myrange.Sort key1:=Sortkey, order1:=xlAscending, MatchCase:=False, Header:=xlNo
    
 End Sub

 

[BloodyBill]

try this:

 Sub test()
 Dim myrange As Range
 Lra = Cells(Rows.Count, "A").End(xlUp).Row
 Lrb = Cells(Rows.Count, "B").End(xlUp).Row
 Lrc = Cells(Rows.Count, "C").End(xlUp).Row
cola = Range(Cells(2, 1), Cells(Lra, 1))
colb = Range(Cells(2, 2), Cells(Lrb, 2))
colc = Range(Cells(2, 3), Cells(Lrc, 3))
hdrs = Range(Cells(1, 1), Cells(1, 3))
Range(Cells(1, 2), Cells(1, 4)) = hdrs
Range(Cells(1, 1), Cells(1, 1)) = "Time"
Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 4)) = ""
Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 1)).NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range(Cells(2, 2), Cells(Lra + Lrb + Lrc, 4)).NumberFormat = "0.000"

Range(Cells(2, 1), Cells(Lra, 1)) = cola
Range(Cells(2, 2), Cells(Lra, 2)) = 1
Range(Cells(Lra + 1, 3), Cells(Lra + Lrb - 1, 3)) = 1
Range(Cells(Lra + 1, 1), Cells(Lra + Lrb - 1, 1)) = colb
Range(Cells(Lra + Lrb + 1, 4), Cells(Lra + Lrb + Lrc - 1, 4)) = 1
Range(Cells(Lra + Lrb + 1, 1), Cells(Lra + Lrb + Lrc - 1, 1)) = colc
    Set myrange = Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 4))
    Set Sortkey = Range(Cells(2, 1), Cells(Lra + Lrb + Lrc, 1))
    myrange.Sort key1:=Sortkey, order1:=xlAscending, MatchCase:=False, Header:=xlNo
   
 End Sub

Wow. Thanks!
Ok, I understand that will be a macro (I'm still a little wet behind the ears.)
Would I highlight an area, then use the macro?
Currently, a wider view of a sheet typically looks like this:

 

[offthelip]

No it just selects the area as per your orignal post, and so it just picks up columns A, B and C from the active sheet.
It is usually best when you post a query to post the whole problem not just a sub set, unless you are happy to modify the solutions. Unfortunately the way I have coded the solution is rather different than the approach I would take if presented with the data in your post 6, So it might not be very helpful

 

[BloodyBill]

No it just selects the area as per your orignal post, and so it just picks up columns A, B and C from the active sheet.
It is usually best when you post a query to post the whole problem not just a sub set, unless you are happy to modify the solutions. Unfortunately the way I have coded the solution is rather different than the approach I would take if presented with the data in your post 6, So it might not be very helpful

Sorry. My bad.