• 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

timbo3185

New Member
Hi, thanks for taking time to view my thread, i'm horrible at Excel and only understand the basics! :( so the struggle is Real!

So please help me, I have attached a file, it contains 4 sheets, but for now i'm only focusing on the sheet "Westall" and the sheet "Down"

The sheet named "Down" contains a timetable for the day and I have highlighted the cells that I am interested in. which are Rows 44 and 45.

I would like to basically transpose the time data from rows 44 and 45 into the "Westall"sheet column F5, however:
  • I want to avoid blank cells, &
  • cells that contain ". . . . ."
I would like to ensure that the cells:
  • only contain times.
  • contain the time that is displayed in the column, regardless if it's in row 44 or 45.
  • times of the trains that do not stop at the station are denoted with an "*" so if possible that should be included, if not then i can find another solution.
  • possibly have spaces inbetween cells.
is it possible to have the cells in the "Down" sheet which I have requested to be avoided, not show up in the "Westall" sheet cells as to minimise space on the sheet if that's possible, ideally I would like to avoid editing the reference sheet at all co I have some very computer illiterate people at work and if it just works, then happy days!

*Please note, as i'm writing this i notice there are one or 2 duplicated columns which i can fix later

so for example:

The data in the reference file looks like this:
Westallarr. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .. . . . .
dep. .. .. .. .04 25. .. .. .. .04 45. .05 0505 05. .. .. .05 26. .. .. .. .05 4605 5406 0206 02. .. .06 06

and I would like the data to look like this: (sorry i can't resize the cell width in the thread)

WTL
04:25​
04:45​
05:05​
05:26​
05:46​
05:54​
06:02​
06:06​

Please let me know if there are any uncertainties cos i might have missed something here lol,

Thanks again for your help!
 

Attachments

timbo3185

New Member
Wow that's fantastic, but is there any way I can do this without? Work has 2016 i believe. And if I do create it with 365, can I still open the sheet in an older version just to print? Or will the formulas not work?
 

p45cal

Well-Known Member
In the attached there's a formula that probably won't work in your version of Excel at cell H5 of the WESTALL sheet. It's there because hopefully the results will remain visible when you open the file so that they can be compared to a user-defined function in cell G5 next to it.
Again, your version of Excel will require that this formula is entered by selecting cells H5:H160 (more cells if there are more times) and entering the formula:
Code:
=TransposeTimesOnly(DOWN!C44:MX45)
into the active cell (which should be cell H5 at the top of the selection if you've selected those cells starting at cell H5) then comitting that formula to the sheet using Ctrl+Shift+Enter rather than the more usual plain Enter.

Note that should there be times in both cells the value in the top cell will be used. I've highlighted in red some of those instances on the DOWN sheet.

By the way, the DOWN!C44:MX45 part can be replaced by any method of referring to a range, for example you could dynamically obtain the two-row range using the station name:
Code:
=TransposeTimesOnly(OFFSET(INDEX(DOWN!$C$7:$MY$105,MATCH("Westall",DOWN!$A$7:$A$105,0),0),0,0,2))
 

Attachments

Peter Bartholomew

Well-Known Member
Sorry but the formulas would need to be refactored (or, as we have seen supplemented by VBA Macros). The LET can be replaced by named formulas or by nested formulas. The FILTER is more of a pain. A standard approach is to test the row to locate times but to record the column numbers. A call to the SMALL function returning the n smallest matches can be used within INDEX to return the filtered arrival/departure times.

It is all very tedious, and I am just thankful that I no longer have need of traditional spreadsheet notation and techniques.
 

p45cal

Well-Known Member
Peter, if you're talking to me, I thought I made it plain that the FILTER formula I used in column H wasn't expected to work (the user said he thought it was Excel 2016) and was only
there because hopefully the results will remain visible when you open the file so that they can be compared to a user-defined function in cell G5 next to it
The user-defined function in vba is to supplant that formula, not supplement it.
If you weren't talking to me, sorry.:)

Edit post posting:
The more I look at it the more I don't think you were talking to me so ignore this drivel.
 

timbo3185

New Member
Thanks everyone for your input in helping me work this spreadsheet out! really appreciate it, i'll take a look at the ones you guys worked on and get back to you :)
 

timbo3185

New Member
In the attached there's a formula that probably won't work in your version of Excel at cell H5 of the WESTALL sheet. It's there because hopefully the results will remain visible when you open the file so that they can be compared to a user-defined function in cell G5 next to it.
Again, your version of Excel will require that this formula is entered by selecting cells H5:H160 (more cells if there are more times) and entering the formula:
Code:
=TransposeTimesOnly(DOWN!C44:MX45)
into the active cell (which should be cell H5 at the top of the selection if you've selected those cells starting at cell H5) then comitting that formula to the sheet using Ctrl+Shift+Enter rather than the more usual plain Enter.

Note that should there be times in both cells the value in the top cell will be used. I've highlighted in red some of those instances on the DOWN sheet.

By the way, the DOWN!C44:MX45 part can be replaced by any method of referring to a range, for example you could dynamically obtain the two-row range using the station name:
Code:
=TransposeTimesOnly(OFFSET(INDEX(DOWN!$C$7:$MY$105,MATCH("Westall",DOWN!$A$7:$A$105,0),0),0,0,2))
I didn't even know there was a function to transpose times only! as for the red times you highlighted, how can we change it to the departing time?

also how are we able to insert a blank row between cells? or is this not possible for such a formula?
 

Peter Bartholomew

Well-Known Member
@p45cal
Don't worry about it :). My remark was only a passing reference to acknowledge the option of using macros.
I went on to examine my assertion that SMALL could be used in place of FILTER. It is possible but I would still need to move from my normal working practice of names and array formulas to the more traditional single cell direct references; something I haven't done since 2014.
Code:
= LET( °, CHAR(160),
  rownum, XMATCH("Westall", Station),
  stops,  INDEX(timingDown, rownum - 1, 0),
  arr,    INDEX(timingDown, rownum + 0, 0),
  dep,    INDEX(timingDown, rownum + 1, 0),
  timing, CONCATENATE(arr, dep),
  timeValue, --SUBSTITUTE(timing, °, ":"),
  timeAdj, IFERROR(timeValue+(timeValue<0.125),""),
  n, COUNT(timeValue),
  k, SEQUENCE(n),
  SMALL(timeAdj, k) )
@timbo3185: @p45cal has used a UDF (a VBA macro aka a User Defined Function)
 

Attachments

p45cal

Well-Known Member
I didn't even know there was a function to transpose times only! as for the red times you highlighted, how can we change it to the departing time?
There isn't. I wrote it as a user-defined function in the file I attached.

also how are we able to insert a blank row between cells? or is this not possible for such a formula?
Both departing times and blanks rows have been addressed in the attachment.
 

Attachments

timbo3185

New Member
Now that I have the Time column sorted, how can i use the Vlookup function to derive other column information, but can you show me in a formula cos I would like to be able to manipulate the formula myself, at least i will feel like i've accomplished something haha:p I have used the Vlookup to derive the row information in a separate project =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) but how do i use it in the row instead of the column?
 

Peter Bartholomew

Well-Known Member
You would need HLOOKUP (tricky looking up times from one of two columns and complication of returning data from rows above the time), LOOKUP (data should be sorted across the row but LOOKUP will ignore errors) or, for 365, XLOOKUP.
 

p45cal

Well-Known Member
to derive other column information
Difficult to work out what information you want in those columns; you have CFD, OAK, WTL, SPG which I guess are shorthand for some of the stops and there are largely times in those rows too, so it looks like you want times in those columns too (and if you want the times to line up with each other in those columns you won't be able to use any of the solutions so far offered because you've explicitly said you want to miss out cells that don't contain times, and they're different on the different rows of the DOWN sheet - do you want them to line up?).
You've got T.D.N. , PLT. and DEST - what are those for? You've got DEP ORIG in one column in the down trains section and separate columns for DEP and ORIG in the up trains section.

I suspect it will be quite hard work to automate or create formulae for all this but it might be possible. I'm also think Power Query (Get & Transform Data) could be useful here.

I see there's another sheet DANDENONG; is that for another similar sheet? Will there be many more?
How often do you have to do this? Every day? Every week?
It might be easier from another source of data - do you have access to anything else? These DOWN and UP sheets will themselves have been created from some other data, it would probably be easier to get the arrangement of data you're looking for from that.
 

Peter Bartholomew

Well-Known Member
I have written a few further formula (on the 'Calcs' and 'DANDENONG' sheets) but I continues to bring them together within a LET function because that is easier for me. Having converted the two-part strings representing times to actual time-values, I was able to search the full timetable for the times from station-specific list and return any other row such as platform.
Code:
= LET( °, CHAR(160),
  rownum, XMATCH("DANDENONG", Station),
  stops,  INDEX(timingDown, rownum - 1, 0),
  arr,  INDEX(timingDown, rownum + 0, 0),
  dep, INDEX(timingDown, rownum + 1, 0),
  timing, CONCATENATE(arr, dep),
  timeValue, --SUBSTITUTE(SUBSTITUTE(timing, °, ":"),"~*",""),
  timeAdj, IFERROR(timeValue+(timeValue<1/6),""),
  n, COUNT(timeValue),
  k, SEQUENCE(n),
  arr∕dep, SMALL(timeAdj, k),
  platform, LOOKUP(arr∕dep, timeAdj, stops),
  platform )
Something tells me this is not going to be a quick job!
 

Attachments

timbo3185

New Member
So the information in the timetable goes as follows:

T.D.N. represents Train describer number, in the reference timetables they have put it down as 'business ID' for some reason but it's the same thing. it's just an identifier for a train, since there are thousands of trains a day over multiple train lines, the trains are grouped into ranges of numbers, different lines use different range of numbers etc. the system has all these numbers in there, all the trains require the number in their radios etc. etc...for me, I'm only interested in the trains that contain information for Westall and Dandenong, hence the Dandenong sheet will contain most of the same information as the Westall with a few differences (more trains and different location times).

The information here is just for one 24 hour period, but luckily this timetable covers Monday to Friday, the Saturday and Sunday timetables are different again but I can easily make a copy of the file and change the timetables over. so this is why I'm just working on one now and hopefully fingers crossed the rest follows quite smoothly.

The times for stops revolving around "WTL" are purely for keeping track of late trains and planning ahead, if it was up to me alone I would just make it simple as hell, but we work with many people and most of these people are not great with change! and always creating these time sheets regularly and contain many errors which on paper isn't an issue, but when you have a train that is going on to the wrong platform or (extremely rare situation) going onto the wrong line then it's a pretty big deal, and the company gets big fines for trains that run over a certain number of minutes late and so on, most of the time errors are just mistakes in putting the wrong time in the location (on the spreadsheet) or the wrong service details which is really just a nuisance and not really a detrimental outcome. Eventually we won't need time keeping anymore but it's always good to have a backup incase we do need them. the other thing is that when we have works being performed on track, we can terminate trains at Westall and go back to the CBD so they issue what's called a circular that contains a modified timetable, so this is also a handy spreadsheet to have cos we could simply drop in the modified timetable and hopefully it just works!

Anyway I might just have to do a reference for each cell in the row to refer to the time corresponding in the range of the arrival and departure.

I'll play around and see if I can come up with something but just want the cell to display the time that the TDN is scheduled through the location then fill down to the end of the document. if I have to fill it all out cell by cell that's fine, I got plenty of time.

thanks for everyone's input so far, really appreciate the help :)
 
Last edited:

p45cal

Well-Known Member
OK, that's all useful background data and helps put things into context.
But.
From my msg#14 in this thread you've told me what TDN stands for, but not much else.
have CFD, OAK, WTL, SPG which I guess are shorthand for some of the stops
Is that right?
so it looks like you want times in those columns
Is that right?
[do] you want the times to line up with each other [horizontally] in those columns?
Important, this one.
PLT. and DEST - what are those for?
Also what data goes into them?
You've got DEP ORIG in one column in the down trains section and separate columns for DEP and ORIG in the up trains section.
Is this a mistake or intentional? What goes into these columns?
It might be easier from another source of data - do you have access to anything else?
?

I don't know where to get these data from on the DOWN sheet. It would help a lot if you filled in a bunch of cells (say in red) on the Westall sheet manually then add a note/comment to each cell describing where that data comes from and perhaps the logic behind finding it on the DOWN sheet, then attach the file here.
 

timbo3185

New Member
Sorry I didn't address your questions earlier,

-CFD, OAK, WTL, SPG are all shorthand for station names that's correct.
-Times for each column that is spot on
Basically one row (in the Westall sheet) shows one train, which is one column (in the down page), I have attached an example which I have removed all but 1 train for ease of reading.
PLT = Platform
DEST = Destination
ORIG = Origin

The railways have always called trains that leave the Central train station of Melbourne a "Down" train, think of the main central location as the top of the proverbial "hill" and all trains run "downhill", or "Uphill" Downs all originate from Flinders Street so one cell is fine, but the UP trains originate from various locations because there are branch lines and depots along the line, so for example, Pakenham and Cranbourne both run towards Dandenong. and from there it's all direct towards Flinder Street.

Pakenham, Cranbourne, Dandenong and Westall all have a yard where trains can originate so there are essentially 4 locations where an UP train can begin their journey so this information is in a separate cell.

I have also attached a version of the checksheet that is all manually entered in which is what we essentially use at work today. (take note that the times will not all match up with the timetable because this is an older timetable this is based off).

I hope this helps clear things up
 

Attachments

Peter Bartholomew

Well-Known Member
Gets quite complicated. Two train 4133s and they are playing dodgems with 4217. That meant the departure times are not sorted so I had to switch back to exact searches with XLOOKUP, so the sheet is not much use for you. I think it is time I abandon ship!

7265172652
 

Peter Bartholomew

Well-Known Member
As a point of interest all trains inbound to London termini are carried on the UP line and outbound is the DOWN line. I think we are looking at an older nomenclature here.
 

timbo3185

New Member
I haven't read through the timetable completely but there are a few duplicate trains because the Friday has some altered trains, if u look at the top in row 5 it will mention if its "Friday only" or "Friday exception" might be something I'll have to work on.

as for the "dodgems" you have to look at the arrival time in the timetable and where these trains are going to understand some of the conflicts, 4217 arrives and waits on the platform for 4 minutes and goes to another line from there, the 4113 stops at Dandenong and turns into another "UP" train, this is where the remarks column comes into play hahaha, yes I agree it's very complicated but one piece of the pie at a time!
 

Peter Bartholomew

Well-Known Member
…the formula's longer than the DOWN sheet;)
I see your point! In its favour, it is doing the same job as the UDF you provided in #4 (now I see I should have opened #10). The one formula, held in a single cell, will complete an entire panel and the formulae for other stations and up line are virtually identical.
 
Top