Combine split shift into one shift - combine data?

[LyndsayYYC]

I have a spreadsheet with data outputted from our scheduling system. Our employees work in multiple locations sometimes in the same shift so the system "divides" their times based on location so one person's shift may be 3 lines in my chart. Is there a way to extract the earliest start time and latest end time and make it display as one shift??

example of data I get... but I need like DICKINSON to be one line showing shift as 0800-1800 instead of 2 lines. (there is also one that is an overnight shift which I am sure is a whole other bucket of fun)

EE	Name	From	To
1	DICKINSON, LINDSAY JOAN	08:00	16:30
1	DICKINSON, LINDSAY JOAN	16:30	18:00
2	SRAN, NAVDEEP	06:00	12:30
2	SRAN, NAVDEEP	12:30	14:00
3	BAJWA, WARDA	10:00	16:30
3	BAJWA, WARDA	16:30	18:00
4	KAHRIMAN, ALBIN	04:30	13:00
4	KAHRIMAN, ALBIN	13:00	14:30
5	SANDHU, AMRINDER	08:05	13:00
5	SANDHU, AMRINDER	13:00	14:30
6	BHULLAR, LOVEJIT	18:00	19:30
6	BHULLAR, LOVEJIT	19:30	04:00

 

[Saba Sabaratnam]

Hi

I have the following data layout.

Enter the following formula in G3

=UNIQUE(A2:A13)

Enter the following formula in H3 and copy it down

=MINIFS(B:B,A:A,A2)

And enter the following formula in I 3 and copy it down

=MAXIFS(C:C,A:A,G3)

Kind regards

Saba

 

[lynzlou]

I can’t get this to work! I just get a #name? return in G3 ?

 

[lrobbo314]

Could use Power Query.

FactSheet_Data_New (8).xlsx
	A	B	C	D	E	F	G	H
1	EE	Name	From	To		Name	From	To
2	1	DICKINSON, LINDSAY JOAN	8:00	16:30		DICKINSON, LINDSAY JOAN	8:00	18:00
3	1	DICKINSON, LINDSAY JOAN	16:30	18:00		SRAN, NAVDEEP	6:00	14:00
4	2	SRAN, NAVDEEP	6:00	12:30		BAJWA, WARDA	10:00	18:00
5	2	SRAN, NAVDEEP	12:30	14:00		KAHRIMAN, ALBIN	4:30	14:30
6	3	BAJWA, WARDA	10:00	16:30		SANDHU, AMRINDER	8:05	14:30
7	3	BAJWA, WARDA	16:30	18:00		BHULLAR, LOVEJIT	18:00	19:30
8	4	KAHRIMAN, ALBIN	4:30	13:00
9	4	KAHRIMAN, ALBIN	13:00	14:30
10	5	SANDHU, AMRINDER	8:05	13:00
11	5	SANDHU, AMRINDER	13:00	14:30
12	6	BHULLAR, LOVEJIT	18:00	19:30
13	6	BHULLAR, LOVEJIT	19:30	4:00
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"From", type time}, {"To", type time}}),
    Group = Table.Group(Type, {"Name"}, {{"From", each List.Min([From]), type nullable time}, {"To", each List.Max([To]), type nullable time}})
in
    Group