Combine Several Rows into one row given the data in the first column
Results 1 to 3 of 3

Thread: Combine Several Rows into one row given the data in the first column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combine Several Rows into one row given the data in the first column

    Hi all,

    I've been working on trying to set up a spreadsheet that others will be able to easily use. I've created a spreadsheet that automatically pulls the data from another worksheet but I can't figure out how to have the different column rows to combine.

    Name Segment Code Date Start Time End Time Duration Memo
    Carrie
    Code: AUU: Unapproved Absence Unpaid
    4/22/2019 6:00:00 AM 9:00:00 AM 0.03:00:00
    4/22/2019 10:00:00 AM 12:00:00 PM 0.02:00:00
    Patty
    Code: APU: Time Off Unpaid
    4/24/2019 Planned time off, advance approval by
    4/25/2019 Planned time off, advance approval
    4/26/2019 Planned time off, advance approval

    This is what I would like for the final outcome:

    Name Segment Code Date Start Time End Time Duration Memo
    Carrie Code: AUU: Unapproved Absence Unpaid 4/22/2019 6:00:00 AM 9:00:00 AM 0.03:00:00
    4/22/2019 10:00:00 AM 12:00:00 PM 0.02:00:00
    Patty Code: APU: Time Off Unpaid 4/24/2019 Planned time off, advance approval by
    4/25/2019 Planned time off, advance approval
    4/26/2019 Planned time off, advance approval

    Any help is much appreciated! Anne

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,883
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine Several Rows into one row given the data in the first column

    Here is a Power Query Solution. The following is the Mcode developed using the UI. Power Query is available in all versions of Excel from 2010 forward. This is a couple of steps and is easy to learn. If you want more information, the book, M is for (Data) Monkey is a great resource for learning how to manipulate data quickly and easily.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Segment Code", type text}, {"Date", type datetime}, {"Start Time", type number}, {"End Time", type number}, {"Duration", type duration}, {"Memo", type text}}),
        #"Filled Down" = Table.FillDown(#"Changed Type",{"Name", "Segment Code"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Date] <> null)),
        #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Start Time", type time}, {"End Time", type time}})
    in
        #"Changed Type1"
    v A B C D E F G
    1 Name Segment Code Date Start Time End Time Duration Memo
    2 Carrie Code: AUU: Unapproved Absence Unpaid 4/22/2019 0:00 6:00:00 AM 9:00:00 AM 0.03:00:00
    3 Carrie Code: AUU: Unapproved Absence Unpaid 4/22/2019 0:00 10:00:00 AM 12:00:00 PM 0.02:00:00
    4 Patty Code: APU: Time Off Unpaid 4/24/2019 0:00 Planned time off, advance approval by
    5 Patty Code: APU: Time Off Unpaid 4/25/2019 0:00 Planned time off, advance approval
    6 Patty Code: APU: Time Off Unpaid 4/26/2019 0:00 Planned time off, advance approval
    Last edited by alansidman; Apr 23rd, 2019 at 04:30 PM.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine Several Rows into one row given the data in the first column

    You are a life saver!!! With a few tweaks, it worked! Thanks so much!!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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