“Data Driven” is the term that is driving me at the moment! I am working at pulling together all the separate data repositories we have at the school into one database, to then be able to run some analytics to inform the decision making at the school. The vision of the end result is a compelling one – having a data dashboard at ones fingertips that will allow a quick and easy way of accessing data on all aspects of the school. The reality of having one’s head stuck in spreadsheets, databases and VBA is not quite as inspiring!
And so it was that I reached a point with a certain spreadsheet where I could do no more. The nearly eighteen thousand rows of data needed to be moved so that some of the rows joined other rows to give in the end, one row per student per subject and there was no way I was going to do that manually! So I began to learn.
I tried to write some formulas to move the data. IF this, THEN do this and this and this. Didn’t work. OFFSET this by this much and then move up and down and OFFSET it again and again and again. A big mess! Use a MACRO to record some steps and then run the MACRO. Disaster. Write a script to do all the moves, and use the Excel coding language VBA to do it. Here I was on to something.
I found a couple of forums with people asking for help with something almost the same. I downloaded a Powerpoint presentation titled “VBA Basics” (obviously designed for someone who knew C++, Python, Java and Klingon) and was thoroughly bamboozled. I almost offered up a project to a crowdsourcing website but was shunted off onto a siding with the idea of posting to a forum to see if anyone could help.
So that’s what I did. Sample spreadsheet uploaded, conundrum sufficiently explained, smilies inserted, I hit submit and bookmarked the page. Maybe someone would read it.
Twenty minutes later, a forum-person (of gender undeterminable by their forum monicker) had posted what could have been the transcript of Spock’s first words to his parents, but what I was reliably informed was VBA, with instructions on how to add it to the Excel spreadsheet and how to make it do its thing. Sixty seconds later I was sitting amazed. Nearly eighteen-thousand rows of data, reduced to one-thousand in under twenty minutes.
So what did I learn today?
In order to accomplish something that you don’t know how to do, you need to do some research to learn as much as you can about what it is you don’t know. When you know exactly what it is you don’t know, and know that there is no way you will know what it is you need to know within the given time constraints, reach out. There is already someone out there who knows, and if you know how to find the person who knows, then maybe they will help you.
In my role as Tech Director of the school I call that a “Massive Win”. But if I was a student in one of the classes in my school, his/her teacher would probably call that a “Massive Cheat”.
I’ll save that conversation for later!