Excel

I often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads out across...
Read more »

The blog post sounds a little Darwinian but I’m afraid it’s just a dull selection of VBA code that enables you to sort one array on the basis of values from another. A double sorting array. I’ve amended array sorting code from John Walkenbach and Anthony’s VBA Tutorials to create something I can understand....
Read more »

Here’s another VBA snippet post. I find it handy to have a store of regularly used macros on the blog so I can access them at any time, not that I need them that often of course. These particular snippets are useful routines for sorting arrays and can be called from other procedures. I’ve...
Read more »

I regularly use simple macros to delete blank rows in my excel spreadsheets and there are loads of examples on internet forums. I’ve recently had to recreate these delete row macros to install on a new computer and thought it would be useful to keep them filed here on the blog. This first macro...
Read more »

I was hunting around for a RANKIF function yesterday so I could rank a load of hospitals in terms of their cost per procedure. Unfortunately RANKIF isn’t one of the available functions but I managed to achieve the same effect by utilising the SUMPRODUCT function. I wrote a SUMPRODUCT tutorial some time ago indicating...
Read more »

I had a query on my sumproduct tutorial that was effectively asking if I could work backwards from an excel crosstab or data table to get back to the simple flat list. Converting a table like this: Back to the data sheet like this: If you didn’t have to do this sort of task...
Read more »

I’ve got a spreadsheet with tabs for each individual directorate. Within the tabs are tables with the same layout but different sizes (number of rows). I want to group together all the used data on each tab and create a single consolidated sheet of continuous data. Obviously I need a macro to copy and...
Read more »

I’ve just published a post detailing a method of removing the errors returned by vlookup or hlookup utilising conditional formatting. I had previously been using the double vlookup method to replace errors with blanks or zeros but then discovered that these excessive lookup functions had a tendency to bloat my spreadsheets. While the conditional...
Read more »

I’ve started working at a new hospital and it seems to be plagued by unwieldy spreadsheets that have a tendency to crawl through saves and then sometimes refuse to open. They are riddled with links and lookups to external spreadsheets and the whole thing feels fairly precarious. I’ve been reading a bit about good...
Read more »

From Excel 2002 onwards you can enter an image, probably a logo, into the header or footer of your worksheet. This code enables you to create a macro that will automate the addition of the logo to every sheet in your workbook. Sub MultipleHeader() ‘created 10/01/2008 by Angela Wolff Application.ScreenUpdating = False ‘ stops...
Read more »