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

[adecredico]

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

<tbody>
</tbody>

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

<tbody>
</tbody>

Any help is much appreciated! Anne

 

[alansidman]

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.

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

 

[adecredico]

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