markster
Well-known Member
- Joined
- May 23, 2002
- Messages
- 579
- Office Version
- 365
- Platform
- Windows
- MacOS
Hello everyone
Right I have a problem that I just can’t solve. I’ve posted something similar before and someone suggested power query but I don’t know it, never used it (although I will do as it looks great) but I have a big scheduling job and not much time so a VBA solution would be the best option for now.
So I have a workbook and there are 3 Sheets
Sheet 1 is a extract of a 1000+ line spreadsheet that I need to split into individual lines based on the data in Column F which relates to week numbers in which teaching is going to take place.
The data in COLUMN F is in the format of week numbers so you will see that it can be entered as a week range e.g. 1-5 or Singles weeks like 1, or several weeks 1,5,6 or a combination of these. So you will see in the example below, COLUMN F3 shows 3-5, 10-12, 16, 19
So Course 2 is being taught on a Wednesday on weeks 3 to 5 and then weeks 10-12 and then on week 16 and week 19.
Sheet 2 contains a list of all week numbers and dates which Sheet 1 should reference
Sheet 3 is the output that I want the Macro to produce
So sticking with the Course 2 example above I need the output to reproduce the date on Sheet 1, but to split out each teaching session onto a separate line so each week of teaching has it’s own line. So in this case the lines are:
Week 3 - 13-Oct-21
Week 4 – 20-Oct-21
Week 5 – 27-Oct-21
Week 10 – 1-Dec-21
Week 11 – 8-Dec-21
Week 12 – 15-Dec-21
Week 16 – 12-Jan-22
Week 19 - 2-Feb-22
So COLUMN F contains a date not a week range.
I have to submit this by 9am on Monday and it’s just going to be impossible to split it out manually. Can anyone help with some code to do this?
Right I have a problem that I just can’t solve. I’ve posted something similar before and someone suggested power query but I don’t know it, never used it (although I will do as it looks great) but I have a big scheduling job and not much time so a VBA solution would be the best option for now.
So I have a workbook and there are 3 Sheets
Sheet 1 is a extract of a 1000+ line spreadsheet that I need to split into individual lines based on the data in Column F which relates to week numbers in which teaching is going to take place.
20210923 - Data Download from Timetabling System.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Reference | Course Name | Room Number | Location | Allocated Teacher | Scheduled Weeks | Scheduled Days | Planned Size | Size | Scheduled Start Time | Scheduled End Time | Duration | ||
2 | 0001 | Course 1 | 23 | London | Teacher 1 | 1, 5, 8-9 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
3 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 3-5, 10-12, 16, 19 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
4 | 0003 | Course 3 | 28 | Manchester | Teacher 3 | 17, 22 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
5 | 0004 | Course 4 | 23 | London | Teacher 4 | 8 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
6 | 0005 | Course 5 | 23 | London | Teacher 5 | 3 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
Sheet1 |
The data in COLUMN F is in the format of week numbers so you will see that it can be entered as a week range e.g. 1-5 or Singles weeks like 1, or several weeks 1,5,6 or a combination of these. So you will see in the example below, COLUMN F3 shows 3-5, 10-12, 16, 19
So Course 2 is being taught on a Wednesday on weeks 3 to 5 and then weeks 10-12 and then on week 16 and week 19.
Sheet 2 contains a list of all week numbers and dates which Sheet 1 should reference
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:F53 | C2 | =B2+1 |
Sheet 3 is the output that I want the Macro to produce
20210923 - Data Download from Timetabling System.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Reference | Course Name | Room Number | Location | Allocated Teacher | Delivery Dates | Scheduled Days | Planned Size | Size | Scheduled Start Time | Scheduled End Time | Duration | ||
2 | 0001 | Course 1 | 23 | London | Teacher 1 | 28/9/2021 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
3 | 0001 | Course 1 | 23 | London | Teacher 1 | 25/10/2021 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
4 | 0001 | Course 1 | 23 | London | Teacher 1 | 16/11/2021 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
5 | 0001 | Course 1 | 23 | London | Teacher 1 | 23/11/2021 | Tuesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
6 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 13/10/2021 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
7 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 20/10/2021 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
8 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 27/10/2021 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
9 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 1/12/2021 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
10 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 8/12/2021 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
11 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 15/12/2021 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
12 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 12/1/2022 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
13 | 0002 | Course 2 | 24 | Birmingham | Teacher 2 | 2/2/2022 | Wednesday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
14 | 0003 | Course 3 | 28 | Manchester | Teacher 3 | 20/1/2022 | Thursday | 0 | 0 | 9:00 | 10:00 | 01:00 | ||
Sheet 3 |
So sticking with the Course 2 example above I need the output to reproduce the date on Sheet 1, but to split out each teaching session onto a separate line so each week of teaching has it’s own line. So in this case the lines are:
Week 3 - 13-Oct-21
Week 4 – 20-Oct-21
Week 5 – 27-Oct-21
Week 10 – 1-Dec-21
Week 11 – 8-Dec-21
Week 12 – 15-Dec-21
Week 16 – 12-Jan-22
Week 19 - 2-Feb-22
So COLUMN F contains a date not a week range.
I have to submit this by 9am on Monday and it’s just going to be impossible to split it out manually. Can anyone help with some code to do this?