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.

  • Quote

    Attached a spreadsheet with sample data in Sheet1 and my manual work in Sheet2. TIA.


    Have I missed something ...???;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for your sample file ...;)


    Are you looking for a solution with Formulas, with a Pivot Table, ... or with a Macro ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

  • Quickly dropped the formula ... since you said you would be interested ...;)


    As soon as I have a moment ... will dive into your macro ..

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I did it with Power Query.


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", Int64.Type}, {"Emp ID", Int64.Type}, {"Date", type date}, {"Shift Time", type text}}),
    4. #"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}})
    5. in
    6. #"Grouped Rows"
  • alansidman :thumbup:


    Well Done !!!


    Love your Power Query solution !!!


    Very elegant and very effective :)


    Edit: ... and you are saving me the time and effort to write the VBA code ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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 :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Could you let me know if you need the macro solution ... or not ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I did it with Power Query.


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", Int64.Type}, {"Emp ID", Int64.Type}, {"Date", type date}, {"Shift Time", type text}}),
    4. #"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}})
    5. in
    6. #"Grouped Rows"

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

  • Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.



    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.



    - Follow this link for an introduction to Power Query functionality.



    - Follow this link for a video which demonstrates how to use Power Query code provided.

  • Hello,


    Attached is your old-fashioned macro test file ;)


    Hope this will still help

    :)



    Edit : ;) Alan is 100 % right :!:

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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.

  • Are you talking about the Test file or you real life file ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Just in case you have many different Excel files simultaneously opened ...


    Would recommend to close down all files and close Excel ... before re-testing the file ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)