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?
how to combine rows in excel
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
how to combine rows in excel
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to combine rows in excel
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 590
- Joined: 14 Nov 2012, 16:06
Re: how to combine rows in excel
I'd suggest a pivottable.
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: how to combine rows in excel
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
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
HansV wrote: ↑01 Feb 2024, 13:38
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
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: how to combine rows in excel
thank you dear i will give it a try and see if it fits my need
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to combine rows in excel
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: how to combine rows in excel
Awesome thank you very much for the code,
-
- 2StarLounger
- Posts: 152
- Joined: 11 Jun 2012, 20:37
Re: how to combine rows in excel
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: how to combine rows in excel
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
could you please mention some of the steps you did in power query?
Regards
-
- 2StarLounger
- Posts: 152
- Joined: 11 Jun 2012, 20:37
Re: how to combine rows in excel
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.
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.
-
- BronzeLounger
- Posts: 1272
- Joined: 01 May 2016, 09:58
Re: how to combine rows in excel
Thank you very much for your kind and informative clarification.
Regards
Regards