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

A

adecredico

New Member
Joined
Apr 23, 2019
Messages
2
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.

[TABLE="width: 932"]
<tbody>[TR]
[TD="width: 124"]Name
[/TD]
[TD="width: 249"]Segment Code
[/TD]
[TD="width: 67"]Date
[/TD]
[TD="width: 82"]Start Time
[/TD]
[TD="width: 79"]End Time
[/TD]
[TD="width: 74"]Duration
[/TD]
[TD="width: 566"]Memo
[/TD]
[/TR]
[TR]
[TD]Carrie
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Code: AUU: Unapproved Absence Unpaid
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4/22/2019
[/TD]
[TD="align: right"]6:00:00 AM
[/TD]
[TD="align: right"]9:00:00 AM
[/TD]
[TD="align: right"]0.03:00:00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/22/2019
[/TD]
[TD="bgcolor: transparent, align: right"]10:00:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12:00:00 PM
[/TD]
[TD="bgcolor: transparent, align: right"]0.02:00:00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Patty
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Code: APU: Time Off Unpaid
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4/24/2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Planned time off, advance approval by
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/25/2019
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Planned time off, advance approval
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4/26/2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Planned time off, advance approval
[/TD]
[/TR]
</tbody>[/TABLE]

This is what I would like for the final outcome:

[TABLE="width: 932"]
<tbody>[TR]
[TD="width: 124"]Name
[/TD]
[TD="width: 249"]Segment Code
[/TD]
[TD="width: 67"]Date
[/TD]
[TD="width: 82"]Start Time
[/TD]
[TD="width: 79"]End Time
[/TD]
[TD="width: 74"]Duration
[/TD]
[TD="width: 566"]Memo
[/TD]
[/TR]
[TR]
[TD]Carrie
[/TD]
[TD]Code: AUU: Unapproved Absence Unpaid
[/TD]
[TD="align: right"]4/22/2019
[/TD]
[TD="align: right"]6:00:00 AM
[/TD]
[TD="align: right"]9:00:00 AM
[/TD]
[TD="align: right"]0.03:00:00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/22/2019
[/TD]
[TD="bgcolor: transparent, align: right"]10:00:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]12:00:00 PM
[/TD]
[TD="bgcolor: transparent, align: right"]0.02:00:00
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Patty
[/TD]
[TD]Code: APU: Time Off Unpaid
[/TD]
[TD="align: right"]4/24/2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Planned time off, advance approval by
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/25/2019
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Planned time off, advance approval
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4/26/2019
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Planned time off, advance approval
[/TD]
[/TR]
</tbody>[/TABLE]

Any help is much appreciated! Anne
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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"

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[td="bgcolor:#ECF0F0, align:center"]G[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Name[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Segment Code[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Date[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Start Time[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]End Time[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Duration[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Memo[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Carrie[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Code: AUU: Unapproved Absence Unpaid[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/22/2019 0:00[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6:00:00 AM[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]9:00:00 AM[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]0.03:00:00[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Carrie[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Code: AUU: Unapproved Absence Unpaid[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/22/2019 0:00[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10:00:00 AM[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]12:00:00 PM[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]0.02:00:00[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Patty[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Code: APU: Time Off Unpaid[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/24/2019 0:00[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Planned time off, advance approval by[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Patty[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Code: APU: Time Off Unpaid[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/25/2019 0:00[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Planned time off, advance approval[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Patty[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Code: APU: Time Off Unpaid[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/26/2019 0:00[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Planned time off, advance approval[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
change data depending on the date
Replies
8
Views
518
etaf
etaf
C
  • Question Question
Countifs?
Replies
5
Views
387
Fluff
Fluff
N
  • Question Question
Join data from second worksheet with delimited list values (mults) to another worksheet with one value per cell/row and keep 1:1 relationship
Replies
2
Views
1K
HeadAgainstABrickWall
H
R
  • Question Question
VB Search a text string
Replies
2
Views
608
rwrichard1
R
J
  • Solved
Problem with Run Time 1004 Error
Replies
6
Views
586
jconkl02
J

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top