Combine split shift into one shift - combine data?

LyndsayYYC

New Member
Joined
Feb 22, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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)
EENameFromTo
1DICKINSON, LINDSAY JOAN08:0016:30
1DICKINSON, LINDSAY JOAN16:3018:00
2SRAN, NAVDEEP06:0012:30
2SRAN, NAVDEEP12:3014:00
3BAJWA, WARDA10:0016:30
3BAJWA, WARDA16:3018:00
4KAHRIMAN, ALBIN04:3013:00
4KAHRIMAN, ALBIN13:0014:30
5SANDHU, AMRINDER08:0513:00
5SANDHU, AMRINDER13:0014:30
6BHULLAR, LOVEJIT18:0019:30
6BHULLAR, LOVEJIT19:3004: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
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
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)



1614050220914.png


Kind regards

Saba
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,360
Office Version
  1. 365
Platform
  1. Windows
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
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
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
Top