Specialized Pivot-like data

I am given a spreadsheet with employee scheduling for a month, I need to create a report specifying Emp ID, Date, Shift and who the employees are in each shift (just the first and last emp ID for the shift is enough). I am currently doing this manually and wanted a way to automate this. Attached a spreadsheet with sample data in Sheet1 and my manual work in Sheet2. TIA.

Apologies, PFA

Preferably a macro please. But I'd be very interested in learning a formula too, because I did try an IF Statement and was able to get the first emp ID, I got stuck after that.

Quote from Carim

Well ... let's get first the Formulas out of the way ...

Attached is your test file

Hope this will help

Display More

That's a nice long formula, thank you .
But I need even the branch codes, dates and shifts to be auto-populated because there are days when there is only one shift, days with different timings, etc. and I need to manually enter them via a pivot currently. Entering the employee IDs is just one extra manual step.

Quote from Carim

Hyperventilate

Should you not be using a ' modern ' Excel version ...

i.e. if you are using a version without Power Query ...

then a macro would become necessary

Display More

Thank you, I did manage to add Power Query into Excel 2010, but I'm not sure how to run the same. A macro would be very helpful too.

Quote from alansidman

I did it with Power Query.

Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", Int64.Type}, {"Emp ID", Int64.Type}, {"Date", type date}, {"Shift Time", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Branch", "Date", "Shift Time"}, {{"MIn", each List.Min([Emp ID]), type nullable number}, {"Max", each List.Max([Emp ID]), type nullable number}})
in
#"Grouped Rows"

Thank you so much for the solution. I'm not familiar with Power Query, but will try it out.

Quote from Carim

Hello,

Attached is your old-fashioned macro test file

Hope this will still help

Edit : Alan is 100 % right

Display More

Thank you again, but nothing happens when I click the 'Create Shift Report' button. It just runs for a few seconds and then stops, the cells remain blank.