Transpose Uneven Rows Into Matching Columns

[Tallonenx]

Hi all, here's another quandary I'm having in getting data to behave nice-

I have a dataset with dates and times things start and stop.
For Ex:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1/1/2017
[/TD]
[TD]12:01 AM
[/TD]
[TD]3:00 AM
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4:30 AM
[/TD]
[TD]9:00 AM
[/TD]
[/TR]
[TR]
[TD]1/5/2017
[/TD]
[TD]1:00 PM
[/TD]
[TD]8:00 PM
[/TD]
[/TR]
[TR]
[TD]1/6/2017
[/TD]
[TD]4:00 PM
[/TD]
[TD]8:00 PM
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12:30 PM
[/TD]
[TD]5:00 PM
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1:00 PM
[/TD]
[TD]2:00 PM
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9:00 AM
[/TD]
[TD]12:30 PM
[/TD]
[/TR]
[TR]
[TD]1/12/2017
[/TD]
[TD]4:00 PM
[/TD]
[TD]8:00 PM
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11:00 PM
[/TD]
[TD]3:00 AM
[/TD]
[/TR]
</tbody>[/TABLE]


There is no overall pattern with how many entries per date these things occur.
They can have as few as 1 or as many as entries as needed.


I'd like to take this data and turn it into this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1/1/2017
[/TD]
[TD]1/5/2017
[/TD]
[TD]1/6/2017
[/TD]
[TD]1/12/2017
[/TD]
[/TR]
[TR]
[TD]12:01 AM
[/TD]
[TD]1:00 PM
[/TD]
[TD]4:00 PM
[/TD]
[TD]4:00 PM
[/TD]
[/TR]
[TR]
[TD]3:00 AM
[/TD]
[TD]8:00 PM
[/TD]
[TD]8:00 PM
[/TD]
[TD]8:00 PM
[/TD]
[/TR]
[TR]
[TD]4:30 AM
[/TD]
[TD][/TD]
[TD]12:30 PM
[/TD]
[TD]11:00 PM
[/TD]
[/TR]
[TR]
[TD]9:00 AM
[/TD]
[TD][/TD]
[TD]5:00 PM
[/TD]
[TD]3:00 AM
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1:00 PM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2:00 PM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9:00 PM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]12:30 PM
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've tried some transpose methods and so far they haven't worked.
They usually result completely losing the corresponding date or jumbled data:

[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
[TABLE="width: 567"]
<colgroup><col><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]1/1/2017
[/TD]
[TD][/TD]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]1/6/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12:01 AM
[/TD]
[TD="align: right"]4:30 AM
[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]12:30 PM
[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[/TR]
[TR]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]9:00 AM
[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]3:00 AM

[/TD]
[/TR]
</tbody>[/TABLE]





Any ideas on how to transpose these uneven rows & columns of data into matching columns,
according to the original date & keeping the time order intact?


-THANKS!

 

[Trebor76]

Hi Tallonenx,

This macro will do the job:

Option Explicit
Sub Macro1()

    Dim lngMyRow    As Long
    Dim lngLastRow  As Long
    Dim lngPasteCol As Long
    Dim lngPasteRow As Long
    Dim strPasteCol As String
    
    Application.ScreenUpdating = False

    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lngMyRow = 2 To lngLastRow
        If Len(Range("A" & lngMyRow)) > 0 Then
            If lngPasteCol = 0 Then
                lngPasteCol = 5 'Initial output column (E in this case). Change to suit if neccessary.
            Else
                lngPasteCol = lngPasteCol + 1
            End If
            strPasteCol = Left(Cells(1, lngPasteCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngPasteCol).Address(True, False)) - 1)
            Range(strPasteCol & "2").Value = Format(Range("A" & lngMyRow), "mm/dd/yyyy") 'Header (date)
        End If
            Range(strPasteCol & Rows.Count).End(xlUp).Offset(1, 0).Value = Format(Range("B" & lngMyRow), "h:mm AM/PM")
            Range(strPasteCol & Rows.Count).End(xlUp).Offset(1, 0).Value = Format(Range("C" & lngMyRow), "h:mm AM/PM")
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Tallonenx]

Thank you so much Trebor76.
I got it to work on one data set.



But then I got an error on the next try-

"Run Time Error 1004"
Method of Range Object '_Global" failed

Debug says that this line is the culprit...
End If
Range(strPasteCol & Rows.Count).End(xlUp).Offset(1, 0).Value = Format(Range("B" & lngMyRow), "h:mm AM/PM")


Not sure what I'm doing wrong :/

 

[Trebor76]

Not sure what I'm doing wrong

Me either I'm afraid

Check what value is being passed to the strPasteCol and lngMyRow variables as I suspect either one or both are not representing a valid column and row number (respectively) i.e. if strPasteCol = "XFE" (Excel 2007 and later) or lngMyRow = 0 you will get that message. I also coded the macro based on the assumption that the date and times were spread across columns A, B and C. This may not be case for each dataset.

HTH

Robert