p45cal
Well-Known Member
Since this is clearly going to be convoluted to automate just now until both you and we know what you want to extract from the DOWN sheet I'm going to suggest an intermediate step to explore if it can go anywhere.
In the attached is just your DOWN sheet which I've made into a proper Table, then I've filtered column 1 (column A) for:
"Business ID", "CAULFIELD", "Days Run Exception", "Dest. Loc. Code", "FLINDERS STREET", "Oakleigh", "Platform No.", "Springvale", "Westall", and blanks.
Then I've manually hidden a few more rows below Springvale: the Platform Nos. and blanks but not the Dest. Loc. Code.
Also manually hidden rows 9,14,16 & 37.
Then I've gone to cell A1 and clicked in its top left corner to select the entire databody (minus the headers) of that table and pressed Ctrl+c on the keyboard (this copies the visible cells to the clipboard).
Then I've right-clicked (say) cell C111 and chosen to paste with transpose.
Then I've made this new range into a proper Excel Table (this makes it easier to move columns about).
Of course, at this stage the headers are all wrong, but you can see what they should be changed to.
Now it's a case of:
1. moving the columns around a bit (select a header cell, then grab its outline and drag left/right)
2. deleting or filtering the rows using various filters in various columns, and perhaps deleting some rows manually.
3. perhaps merging the Westall column and Column5 into one.
If this is getting close to the result you want, then a good deal of it can be automated (perhaps with Power Query, perhaps a macro, maybe both), but I'd ask that you do it for a few weeks manually so that later you can tell us what you're doing which is exactly the same week-in, week-out, and the exceptions.
In the attached is just your DOWN sheet which I've made into a proper Table, then I've filtered column 1 (column A) for:
"Business ID", "CAULFIELD", "Days Run Exception", "Dest. Loc. Code", "FLINDERS STREET", "Oakleigh", "Platform No.", "Springvale", "Westall", and blanks.
Then I've manually hidden a few more rows below Springvale: the Platform Nos. and blanks but not the Dest. Loc. Code.
Also manually hidden rows 9,14,16 & 37.
Then I've gone to cell A1 and clicked in its top left corner to select the entire databody (minus the headers) of that table and pressed Ctrl+c on the keyboard (this copies the visible cells to the clipboard).
Then I've right-clicked (say) cell C111 and chosen to paste with transpose.
Then I've made this new range into a proper Excel Table (this makes it easier to move columns about).
Of course, at this stage the headers are all wrong, but you can see what they should be changed to.
Now it's a case of:
1. moving the columns around a bit (select a header cell, then grab its outline and drag left/right)
2. deleting or filtering the rows using various filters in various columns, and perhaps deleting some rows manually.
3. perhaps merging the Westall column and Column5 into one.
If this is getting close to the result you want, then a good deal of it can be automated (perhaps with Power Query, perhaps a macro, maybe both), but I'd ask that you do it for a few weeks manually so that later you can tell us what you're doing which is exactly the same week-in, week-out, and the exceptions.
