I don’t use Excel that much these days. Increasingly, I’m having to work with large relational databases, some of which are 50, 60GB big, and Excel doesn’t really play well with that much data.
However, sometimes, when you export a report, there’s nothing as easy as loading it into Excel, and messing around with it there. And probably messing around with it involves doing a vlookup.
Now, I’ll put it out there: I think I’m quite good at Excel. Which was why I was surprised to read this article about how vlookup is the wrong way of doing this sort of thing.
Index and Match are actually two functions, so it means passing the results of one function another. This is actually quite an advanced skill, so I guess this is why no one does this.
However, there’s one key thing here that makes Index and Match much, much faster than vlookup. And if there’s one reason you should switch, this is it:
Suppose you have a large table with many columns of product information. And suppose you want to look up a specific SKU in the table and return information about it from a variety of columns within the table.
If you use VLOOKUP you must look up the same SKU for each column of information you need. Those duplicate lookups take a long time to perform.
But if you use an INDEX-MATCH approach, you could set up one MATCH formula that returns only the row-index number for the product that interests you. Then you can use any number of INDEX formulas that get their row-index number from the cell with that single MATCH formula. Both MATCH and VLOOKUP take about the same time to calculate. But INDEX works almost instantly. So if you want to return ten items for a SKU, the VLOOKUP method will take roughly ten times longer than the INDEX-MATCH approach.
Email is great. But sometimes to solve something you need to speak to people in person. Knowing when is a really key skill.
I’ve finished phase 1 of the software I had to build yesterday.
It’s a little tricky as it involves a client I’ve built that sits on the local machine, sending data to a remote website, then a script picking up that data, and loading it into a database behind our firewall that isn’t available through the internet.
I guess it would be possible to VPN the data directly into that database, but that seems a bit of overkill.
All in all, at the moment, it’s only about 100 lines of code. And I’m happy with the simplicity and neatness of it.
I’m pretty sure that there will be some fixes I need to make to it when we start testing, but I’ve built that into my plan – in a way that I never used to do. I used to be very optimistic (“It probably works, any problems will be small”) whereas now I’m being quite pessimistic (“There are probably problems, must have time to capture them”).
In fact, my hope is that with it’s simplicity therewon’tbe many problems, but I’m planning for them anyway. What’s that they say? “Hope for the best but plan for the worst”.
Something has come up that involves me writing some code. The first thing I’ve done is get a very clear scope of what it covers (and what it doesn’t). I’m also thinking about the simplest way to do it. As we know the best code is none at all but this seems to be something that would require loads if manual work or a small amount if code. I’m keeping my eye on the scope.
Management like PowerPoint. They love charts and summaries. You need to give them something like this every week/month and then they’ll be happy.
I’m producing a communications plan at the moment at work, to try to hit lots of these at once. It’s actually incredibly important – after all, if they don’t know you’ve done it, then you might as well not have bothered.
My boss has just come back from maternity leave, and a slight danger of her deploying some micromanagement. So to get in there quickly, and keep her happy and feel in control I need to put together a regular “pack” of stuff for her. She might not read it, but at least if she can see it or know it’s coming, she’ll be satisfied I have things in control.
And actually, depressing as this is, sometimes more important than actually having it under control.
I sat down at lunch with my kindle and it was really nice. Lesson: always have a lunch break.
Yesterday, I went back to University to get my fake MA.
It was a really strange experience, partly because I was staying in a room that looked just like my room from the first year, seven years ago. Suddenly, I had flooding back to me all my feelings, emotions, hopes and aspirations of that time.
My life has turned out in a very different direction than I thought it would. In some ways, I have exactly the job and lifestyle I wanted. In other ways, there are some things I’ve forgotten that I wanted to do.
It’s not that I’ve particularly missed anything, but it did remind me of what used to be important to me. It was really useful for getting outside my head for a bit, and thinking beyond the bustle and churn of life in London. It’s difficult to stand back and squint sometimes – life just gets in the way.
I’m not necessarily sure if I’ll change anything or do anything different. But it reminded me that Icoulddo things; that there are other options. It also reminded me of things I thought and wanted to do that I’d forgotten about.
It was a surprisingly emotional day really.