Page 1 of 2 1 2 LastLast
Results 1 to 10 of 20

Thread: Merge Two columns based on date

  1. #1
    Neophyte mmoore5553's Avatar
    Join Date
    Mar 2019
    Posts
    4
    Articles
    0
    Excel Version
    office 365 excel

    Merge Two columns based on date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a power query which it is basically a time clock.

    I have a date and user first name and last and time they clocked in and then clocked out. I need to combine the the two columns based on the date and the user first name and last name. See example below.

    The final result would be

    3/4/2019 megan cunningham 8:58 am 2:17 am 3:17 am 5:58 am
    3/5/2019 megan cunningham 9:00 am 2:32 am 3:37 am 6:14 am

    This would be done for each date .

    it is basically merging any other column that matches the same date and same username and adding the other times. I have tried to group by and then do a list but i could only bring in one value. Any help would be appreciated. I can share the excel sheet if needed. Click image for larger version. 

Name:	excel_help.PNG 
Views:	18 
Size:	64.8 KB 
ID:	8956

  2. #2
    Acolyte alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    92
    Articles
    0
    Excel Version
    2019
    Pictures
    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.


    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.


    2. Make sure that your desired results are also shown (mock up the results manually).


    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).


    4. Try to avoid using merged cells as they cause lots of problems.


    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  3. #3
    Neophyte mmoore5553's Avatar
    Join Date
    Mar 2019
    Posts
    4
    Articles
    0
    Excel Version
    office 365 excel
    Thank you alansidman. I have updated the spreadsheet with a results tab on how the data should look after it is done.
    Attached Files Attached Files

  4. #4
    Neophyte mmoore5553's Avatar
    Join Date
    Mar 2019
    Posts
    4
    Articles
    0
    Excel Version
    office 365 excel
    Please disregard. I had to hire someone on freelancer.com to get it working.

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,132
    Articles
    0
    Excel Version
    Office 365 Subscription
    Please share the solution here. Thanks.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Neophyte mmoore5553's Avatar
    Join Date
    Mar 2019
    Posts
    4
    Articles
    0
    Excel Version
    office 365 excel
    Quote Originally Posted by AliGW View Post
    Please share the solution here. Thanks.

    Here is the solution. Trust me I only understand half the code.
    Attached Files Attached Files

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,132
    Articles
    0
    Excel Version
    Office 365 Subscription
    Thanks - that's useful for others looking for similar solutions.

    However, for anyone reading this, it's not a PowerQuery solution: it's VBA.
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    42
    Articles
    0
    Excel Version
    Office 365
    Though I'm sure the solution works, it's very novice type code.
    Oh... by the way, YOU'RE WELCOME!

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,132
    Articles
    0
    Excel Version
    Office 365 Subscription
    On its own, that's a fairly useless observation, Nick. I hope you are going to go on show us what more expert code to do the same thing would look like and explain why.
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #10
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    22
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Here's a Power Query solution. I grouped by employee and date, then used a trick I learned from Ken to assign an index to each group.

    https://www.excelguru.ca/blog/2018/0...g-power-query/

    Merged the time in and time out into a single column and then pivoted this on the index. This revealed that some of the employees came and went as many as five times in a day. It wasn't clear how the OP wanted to handle this since she only shows two pairs in her desired results; it looks like the VB solution simply takes the first and last entry/exit on any given day.

    Turns out that, after pivoting, there were 5 columns that needed unmerging. I did them one-by-one, but is there a more elegant way to do this that would future-proof the solution in case more or less columns resulted from the pivot operation?

    Timecard report by Department PQ3.xlsx

    Norm

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •