Hi,
The following table contains raw data for conversion.
ID Prog Code Name Tot Stud Lect H Lect Max Tute H Tute Max WS H WS Max Part Order Day
1 Prog 1 Cs 1 Cs Nm 1 200 2 50 1 25 1 25 AM Yes Mon
2 Prog 1 Cs 2 Cs Nm 2 50 1 35 2 35 AM No Any
3 Prog 1 Cs 3 Cs Nm 3 75 2 35 1 35 1 35 PM No Any
4 Prog 1 Cs 4 Cs Nm 4 250 2 50 1 25 1 25 Any Yes Tue
5 Prog 1 Cs 5 Cs Nm 5 125 2 50 2 25 Night Yes Any
6 Prog 1 Cs 6 Cs Nm 6 400 1 50 2 50 1 50 PM Yes Fri
7 Prog 1 Cs 7 Cs Nm 7 20 2 35 1 35 1 35 PM No Any
8 Prog 1 Cs 8 Cs Nm 8 15 1 40 1 20 AM No Any
9 Prog 2 Cs 9 Cs Nm 9 4 2 35 1 35 1 35 PM Yes Any
10 Prog 2 Cs 10 Cs Nm 10 105 1 50 1 50 Any No Fri
11 Prog 2 Cs 11 Cs Nm 11 16 2 35 1 35 1 35 Night Yes Any
12 Prog 2 Cs 12 Cs Nm 12 280 2 50 2 50 AM Yes Any
13 Prog 2 Cs 13 Cs Nm 13 100 2 35 1 35 PM Yes Mon
14 Prog 2 Cs 14 Cs Nm 14 249 1 50 2 25 AM Yes Tue
15 Prog 2 Cs 3 Cs Nm 3 49 2 35 1 35 1 35 AM No Wed
16 Prog 2 Cs 7 Cs Nm 7 81 2 35 1 35 1 35 Any Yes Any
17 Prog 3 Cs 15 Cs Nm 15 36 2 50 2 50 Night No Any
18 Prog 3 Cs 2 Cs Nm 2 99 1 35 2 35 AM No Any
19 Prog 3 Cs 16 Cs Nm 16 108 2 40 1 20 1 20 PM Yes Thurs
20 Prog 3 Cs 17 Cs Nm 12 15 2 50 2 50 Night No Any
This data is contained in a sheet named Courses.
All Programs have unique course codes and names.
A course may be in multiple programs.
Tot Stud is the total number of students enrolled in a Prog_Course
Students in each Prog_Course have different types of delivery modes Lecture, Tutorial, Workshop. These modes have durations [Lect H, Tute H, WS H]. Some Prog_Courses have no lect, no tute, or no WS as defined in the above table. Most collections of Mode of Delivery total 4 hours but not all.
Each complete set of students enrolled in a Prog_Courses are broken into groups for each delivery mode according to the above table [Lect Max, Tute Max, WS Max]. Call these classes for this problem.
Required:
Code to dump to another worksheet [Classes] so that the following table is created:
Course_ID Group ID Prog Cs Code Cs Nm Lect Tute WS Part Order Day
1 1 Prog 1 Cs 1 Cs Nm 1 2 1 1 AM Yes Mon
1 1 Prog 1 Cs 1 Cs Nm 1 0 1 1 AM Yes Mon
1 2 Prog 1 Cs 1 Cs Nm 1 2 1 1 AM Yes Mon
1 2 Prog 1 Cs 1 Cs Nm 1 0 1 1 AM Yes Mon
1 3 Prog 1 Cs 1 Cs Nm 1 2 1 1 AM Yes Mon
1 3 Prog 1 Cs 1 Cs Nm 1 0 1 1 AM Yes Mon
1 4 Prog 1 Cs 1 Cs Nm 1 2 1 1 AM Yes Mon
1 4 Prog 1 Cs 1 Cs Nm 1 0 1 1 AM Yes Mon
2 1 Prog 1 Cs 2 Cs Nm 2 1 2 1 AM No Any
2 2 Prog 1 Cs 2 Cs Nm 2 1 2 1 AM No Any
2 3 Prog 1 Cs 2 Cs Nm 2 1 2 1 AM No Any
2 4 Prog 1 Cs 2 Cs Nm 2 1 2 1 AM No Any
2 5 Prog 1 Cs 2 Cs Nm 2 1 2 1 AM No Any
Etc, etc.
The following algorithm provides a skeleton of how this was achieved:
First, find all rows in Courses where the Course Code is the same [like ID 2 and 18], and
: check that the Lect H, Lect Max, Tute H, Tute Max, WS H, WS Max, Part, Order, and Day data in each row for that course all match. If they do not, provide an error message and halt execution to allow the user to remedy set focus on the first cell containing an error.
: test that if Tute Max or WS Max are less than Lect Max, they are always exactly half the value of Lect Max. This should also be tested at the beginning, error message provided, set focus etc.
: if all the above is OK, then for each set of multiple occurrences of courses, add the Tot Studs together and store in memory.
Then, for every record in Courses [some combined due to the last possibility] divide the Tot Stud by the largest of Lect Max, Tute Max, or WS Max, and round up to nearest integer call these Classes for the following.
Create records in Courses so that each Class has a record replicate the number of hours and other variables for each ensure that Group ID links back to the Course_ID from that table.
Some other rules/notes:
In the Classes table, all data must be present for each record if there are no lect, tute, or ws hours, then place a 0.
When there are multiple programs containing the same course [as in 2, 18], use the prog code from the first occurrence in Classes.
Pragmatic Notes:
Code must be self-documenting [or liberal use of rems] so that modification is simplified. Use only meaningful variable names.
Use arrays in VBA is you like, but must not be static.
Option Explicit is a must.
Modularising into smaller chunks [procedures] is highly desirable.
I am kick-starting this at 500, but am fully aware that this is a significant task, that there is potential for requirements creeping, etc, and therefore am happy to chuck more points in as required.
If you work with the above Course table, then that is the table I have in my workbook, and we will be able to compare and discuss outcomes and issues etc.
Be patient with me, I am a formula person in XL, so am on a learning curve.
Dave
