how to combine rows in excel

[siamandm]

Hello everyone,
the screenshot below is a sample data of hundreds rows, how to combine the check in and check out time for the same day using an automated way or vba code?

Sample.xlsx

[HansV]

Here you go

Code: Select all

Sub CombineRows()
    Dim tbl As ListObject
    Dim r As Long
    Application.ScreenUpdating = False
    Set tbl = ActiveSheet.ListObjects(1)
    r = tbl.ListRows.Count
    Do
        If tbl.ListRows(r).Range(1, 5).Value <> "" Then
            tbl.ListRows(r - 1).Range(1, 5).Value = tbl.ListRows(r).Range(1, 5).Value
            tbl.ListRows(r).Delete
            r = r - 1
        End If
        r = r - 1
    Loop Until r < 2
    Application.ScreenUpdating = True
End Sub

[snb]

I'd suggest a pivottable.

[siamandm]

Dear Hans,
Thank you very much for the code it works very good, but I would like to get the result in new sheet and keep the original one

Here you go

Code: Select all

Sub CombineRows()
    Dim tbl As ListObject
    Dim r As Long
    Application.ScreenUpdating = False
    Set tbl = ActiveSheet.ListObjects(1)
    r = tbl.ListRows.Count
    Do
        If tbl.ListRows(r).Range(1, 5).Value <> "" Then
            tbl.ListRows(r - 1).Range(1, 5).Value = tbl.ListRows(r).Range(1, 5).Value
            tbl.ListRows(r).Delete
            r = r - 1
        End If
        r = r - 1
    Loop Until r < 2
    Application.ScreenUpdating = True
End Sub

[siamandm]

thank you dear i will give it a try and see if it fits my need

I'd suggest a pivottable.

[HansV]

Code: Select all

Sub CombineRows()
    Dim tbl As ListObject
    Dim r As Long
    Application.ScreenUpdating = False
    ActiveSheet.Copy
    Set tbl = ActiveSheet.ListObjects(1)
    r = tbl.ListRows.Count
    Do
        If tbl.ListRows(r).Range(1, 5).Value <> "" Then
            tbl.ListRows(r - 1).Range(1, 5).Value = tbl.ListRows(r).Range(1, 5).Value
            tbl.ListRows(r).Delete
            r = r - 1
        End If
        r = r - 1
    Loop Until r < 2
    Application.ScreenUpdating = True
End Sub

[siamandm]

Awesome thank you very much for the code,

[p45cal]

The attached has a Power Query solution.
It seems to handle different dates and different IDs correctly, while also converting the string dates and times to proper Excel dates and times.
See table at cell O1.
Should you change the data in the table at cell A1 (it's called Table4), you'll need to refresh the query by right-clicking somewhere in the results table and choosing Refresh.
I've removed the Event column because it's misleading and superfluous.

[siamandm]

Thank you very much @p45cal this made me to consider learning Power query as it seems to be very useful..

could you please mention some of the steps you did in power query?
Regards

[p45cal]

Power Query steps, after grabbing the table:
Converted the date columns and time columns to date and time. I used with locale and set that to UK style because I noted that the date strings were in the UK style D/M/Y.
Removed the Event column
Selected the Check-in and Check-out columns and used Unpivot only selected columns
Selected the Attribute column and chose Pivot Column, then in the dialogue box chose Value as the Value field, and Don't Aggregate in the Advanced options.

[siamandm]

Thank you very much for your kind and informative clarification.

Regards