I do it all the time. When I get a new piece of software, I don’t necessarily want to know what it can do. I generally have a good idea.
I want to know what more it can do.
My routine is to work my way across the menus, pulling them down, flying them out if necessary, and getting the big picture. Quite frankly, there are often way more options than I know that I’ll ever use. But, that’s OK. I’ve seen them – just in case.
Sometimes I actually find a routine that saves my bacon – maybe not at the moment but at some time in the future.
I had such an event in preparing for the Bring IT, Together conference.
If you attended, you’ll know that outside the session rooms, there was a digital monitor that displayed the session currently being offered. Now, we don’t ask the presenters to do that! We did it. Well, actually we provided the data and someone at the convention centre piped it into the displays.
I got a last minute request to prepare a .csv file with the time, room, presenter, and session title for each session for each of the three days. There were somewhere around 250 entries to create.
Now, when I said that I would do it, I thought it would be a piece of cake. After all, all of the conference planning and documents were sitting in a shared Google Drive folder. I’d just extract what I needed and do an export to .csv format.
Upon further inspection, this turned out to be more that a quick export. We had a document that we called a “Minute by Minute” which detailed everything that we planned to happen for the conference. The sessions offered was embedded in that document. If you picked up a paper copy of the planner, you basically have what I was working with.
There was one catch though.
The document was formatted for human reading. The columns weren’t sorted by time; they were sorted by room. As I looked, there was Room 101 and then what was scheduled for 10:00, then 11:00, then 12:00, etc. The file that I needed to create was to be formatted by time. At 9:00, these sessions were offered in Room 101, 102, 201, etc. It wasn’t a huge deal, it was just a matter of taking the timetable which was in this big matrix and changing the orientation.
Then, there was one final step.
I’ll use, as an example, the person who will first read this post – Aviva Dunsiger. She was offering this session.
Everything I needed was in there. It wasn’t just the way I needed it.
I needed the presenter(s) names in one cell and their session descriptor in another. I was about to settle in for a bit of copying and pasting until something nagged me in the back of my mind. I should be able to write a script to do this. Checking all the descriptors, there was a dash(-) between the presenter name and the title. I could just write a script to take what was left of the dash and put it in one column and then what was right of the dash and put it in another column. But something still nagged. I went looking and found why I was hearing those voices.
In the Data menu, there was an option to “Split text to columns”. I remember doing it once just to see how it worked. I typed a few full names into cells with a space between first and last name and used that option to split them into different columns.
Could I use it here?
You know the answer. This wouldn’t be much of a blog post if I couldn’t report success.
The dash wasn’t one of the default separators but “Custom” allowed me to select the dash. It was just a matter of highlighting the entire range of sessions and applying this to the range. Easy peasy. Well, almost. Just remember that a computer will only do what you tell it to do. There were a few sessions that had also included a dash in the descriptor. I ended up with a little more splitting than I had expected! But that’s why they put erasers on pencils for. Right? It was just a matter of putting the outliers back together.
It was an interesting reflection on my part about how I’d seen this obscure menu item at one point and never really had had a use for it. It paid off with its effectiveness. It was much more efficient than copying/pasting or writing/testing/applying my own script.
I love it when a plan comes together.