Combine split shift into one shift - combine data?

LyndsayYYC

New Member
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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Saba Sabaratnam

Active Member
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

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

lrobbo314

Well-known Member
Could use Power Query.

FactSheet_Data_New (8).xlsx
ABCDEFGH
1EENameFromToNameFromTo
21DICKINSON, LINDSAY JOAN8:0016:30DICKINSON, LINDSAY JOAN8:0018:00
31DICKINSON, LINDSAY JOAN16:3018:00SRAN, NAVDEEP6:0014:00
42SRAN, NAVDEEP6:0012:30BAJWA, WARDA10:0018:00
52SRAN, NAVDEEP12:3014:00KAHRIMAN, ALBIN4:3014:30
63BAJWA, WARDA10:0016:30SANDHU, AMRINDER8:0514:30
73BAJWA, WARDA16:3018:00BHULLAR, LOVEJIT18:0019:30
84KAHRIMAN, ALBIN4:3013:00
94KAHRIMAN, ALBIN13:0014:30
105SANDHU, AMRINDER8:0513:00
115SANDHU, AMRINDER13:0014:30
126BHULLAR, LOVEJIT18:0019:30
136BHULLAR, LOVEJIT19:304:00
Sheet4

Power Query:
``````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``````

Replies
1
Views
67
Replies
0
Views
195
Replies
1
Views
347
Replies
5
Views
138
Replies
6
Views
302

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,588
Messages
5,765,303
Members
425,271
Latest member
kristyfinn

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.

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

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