Automatically populating timetable from a list of chapters and their durations

[LMF5000]

We regularly do this task manually and are looking to automate it. We have a list of chapters, we know their duration in hours and we know the sequence in which they should be done on the timetable.

I've attached the input (worksheet "list") and desired output (worksheet "timetable").

Each cell in the timetable represents one hour. In each day there are 6 slots where chapters are done, and two blank slots for coffee breaks. The timetable is split up into weeks (Monday-Friday). No training is done on weekends so they aren't populated (however depending on course the weekend could be Fri/Sat or Sat/Sun).

I'd like to have the sheet automatically pick each chapter from the list, place them into the timetable, and repeat for as many times as written in the hours column (eg if 3 hours, it fills 3 cells with the chapter).

It would be really great if it could also add days automatically as needed (i.e. keep appending days, weekends and new weeks as necessary until all chapters are placed).

It would be even more great if I could provide a list of public holidays by date (dd/mm/yy), and have it skip those days (i.e. put them in the timetable but leave them unpopulated, like weekends)

Formulae or VBA would both be suitable - though if anyone knows of any software that can do this better than excel I'm eager to know as well

[Olly]

You can use Power Query / Power Pivot for this.

Format your Chapter list as a table (tblChapters), add another table for your list of holiday dates (tblHolidays) and add a named range to TimeTable!B9 (StartDate)

Add the following Power Query, and Load To Data Model:

Please Login or Register  to view this content.

Add the following Measure to the Data Model:

Please Login or Register  to view this content.

Now you can use the following formula in cells B10:H17 and J10:P17 (note: stop merging cells!)

Please Login or Register  to view this content.

To update, simply amend / update your Chapter list / Holiday list / Start Date, then click Data > Refresh All.

[LMF5000]

Wow. I've been using excel for 20 years and didn't even know Power Query/Power pivot existed . Thank you so much!

I was able to retrace your steps, except adding the measure to the data model (it's not in the link in your signature either) - where is the menu option for that?

Also, if I want to extend this to cover 6 weeks or so, do I simply copy the formula to more cells on the right?

Lastly, I want to extend this so we can basically populate the "list" (i.e. list of chapters and hours) by picking from a drop-down list of existing courses - each course has its own sequence of chapters and hours which we'll know beforehand.

Example -
Course 1 will have:

• 3 hours chapter 1
• 3 hours chapter 2
• 3 hours chapter 3

Then course 2 will have:

• 1 hour chapter 1
• 2 hours chapter 4
• 3 hours chapter 7

The user chooses a course from the list (course1, course2 etc.), enters a start date (which becomes cell B9 in the timetable sheet) and the timetable is populated.

For the public holidays I had prepared a list picker with vlookup/hlookup where I had all the holidays in a particular country and the user simply selects the country from a drop-down list, but perhaps that would be more work than just putting the particular public holidays in every time a new course is launched.

[Olly]

To add a measure to your data model, use the Power Pivot ribbon > Calculations > Measures > Manage Measures...

To extend the output date range, yes, simply copy the date formulae and cubevalue formulate to more cells to the right.

Bit of scope creep to introduce multiple courses / countries - but if your workflow is to make the parameter selections then refresh the timetable, then you just need to modify the input mechanism of your tables. If you wanted something a bit more dynamic, then we could probably build a power pivot model using multiple tables, so that the course / country start date selection could be dynamic on the output. Give me some source data to play with, and I'll have a look...

[LMF5000]

Fair enough - I omitted the multiple courses bit to start with the essential function so as not to overwhelm any responders. So, to keep things small and easy, let's just do the two courses (course1 and course2) with chapters/hours from post #3, and for holidays the following two partial lists (naturally I will add the full lists myself before deployment).

Malta (d/m/y):
1/1/2020
10/2/2020
19/03/2020

Amsterdam:
1/1/2020
10/4/2020
12/4/2020

There needs to be the ability to append custom dates to the holiday list (eg if a particular customer has leave days). We also regularly add new courses and new countries so it should be possible to append to these lists as well.

Don't know if this is relevant, but each timetable will be saved in its own separate file, so no worries if the ad-hoc holidays are just added to the end of the holiday dates list - the timetable won't get re-used for another customer.

[LMF5000]

So, any ideas? The best I can come up with is having a big list of lists array and then using vlookup and hlookup to populate the source list for the timetable thing by matching with what they selected in the drop-down list.

[JeteMc]

A bit hard to suggest ideas without seeing an updated (incorporating procedures in Olly's posts) file.
That said, if the list of lists is an array then I feel that INDEX/MATCH(row_no.)/MATCH(column_no.) will work better than VLOOKUP and HLOOKUP.
Let us know if you have any questions.

[LMF5000]

A bit hard to suggest ideas without seeing an updated (incorporating procedures in Olly's posts) file.
That said, if the list of lists is an array then I feel that INDEX/MATCH(row_no.)/MATCH(column_no.) will work better than VLOOKUP and HLOOKUP.
Let us know if you have any questions.

Olly included the sheet with all his code implemented as an attachment in his post - is that what you mean?

He's been rather quiet after my last post. Hope I haven't annoyed him by changing the scope like I did. I appreciate all the help - I've given rep points to every helpful post here on my thread to make up for it.

[Olly]

Of course I'm not annoyed - just busy. Have in mind a Power Pivot solution which fully integrates your requirements, just not found time to sit down and write the code, yet. Hang tight - will get to it this week.

[Olly]

Here's a solution, using Power Pivot.

Populate the four Input tables:
1. tblChapters contains Course / Chapter data, in sequential order
2. tblHolidays contains holidays per country
3. tblTimeSlots controls which timeslots are available
4. tblWeekdays controls which weekdays are used

Refresh all data.

Now on the Output sheet, you can select which course(s), the relevant Country (to determine appropriate holidays) and the start date, then the timetable is populated as required, dynamically.

Capture.PNG

There are quite a few DAX measures in the model, to make this work. Let me know if you would like me to step through them, and explain what they're doing. Essentially, we are determining a sequence of chapters, and a sequence of available timeslots, then matching the two, and populating a timetable crosstab based on the matches.

[LMF5000]

Hi Olly,

Thanks for that!

I'm a little out of my depth with PivotTables and PowerPivot. As in, I know almost nothing about them. I don't know how to change the formatting of the output. For example, the course name will be very long (an entire sentence in fact) so that would be better as the title, and not in each individual cell. How would I change and amend that?

We have over 40 courses, each with 40-ish chapters so the list will get long - though I guess we could probably use your filters to clean up the list in that case.

Last question - if I want to add a "subtype" to each course, how would I do that? So input of Course1 Type1 gives a particular chapter list, input of Course1 Type 2 gives another list.