• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transposing multiple rows of data into 1 column in excel with a few rules

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.
 

Attachments

timbo3185

New Member
I might have to rethink how i'm going to approach this, it's getting way too complicated and i feel bad for making you put all this effort into something in your spare time lol!
 

timbo3185

New Member
for Cell A5 in the westall sheet, it possible to make a simple formula to check if (G5 is equal to the cell in DOWN sheet range of rows 44:45), if true then use the cell content of row 3 in WESTALL sheet, and for the column number can we put a formula to use the column number of the cell that G5 is equal to?
 

timbo3185

New Member
Hi guys, after a bit of time away from work and just forgetting about the project I am rethinking how to fill out this spreadsheet,

Is there a way we can generate a list of train numbers, only including trains that travel down the line? so if we look back at the reference timetable, any numerical value in row 3 that corresponds in rows 44 or 45 to include in the list and the rest to be ignored?
 
Top