Helpful Excel function – Remove blank values from a list

Just a quick note, more to myself than anyone else.

Today I was asked by the BA in my team to look into a particular problem trying to be solved within an Excel workbook.

I know, I know, don’t get me started on companies using overly engineered Excel workbooks to run their business…

Moving right along.

The problem was based on a list of data, if a column value of a row was set, display the key of that row in a list.

i.e.
Table of RSVPs to a list of attendees.

Upon first glance I though this would be very simple, and in any query language it would be.

However when I actually sat down to look at the problem, I realised I could get the list very easily, but displaying it without blank values was a problem.

i.e.
With a simple =IF(B2=”Yes”,A2,”") formula I could produce

Removing those blanks however was a little more interesting.

Due to my before mentioned “dislike” for overly engineered Excel workbooks, I told myself I would not revert to doing it in a VBA Macro, I would solve this with a formula.

Eventually I stumbled across this helpful little function at http://www.cpearson.com/excel/noblanks.aspx

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

While not easy to read, when copied into a range of cells the same size as the original list (including blanks), this formula will populate said range with the non blank values sequentially.

So I took my first function to create the temporary list of attendees (including blanks).

And then use the above formula to select the values from this list without the blanks.

Now this is a simple contrived example but I have tested this with 1000 rows of data with up to 30 character strings as the keys and it still is almost instant performance.

One thing to note: If you don’t actually follow the above link. This is an array function, therefore when entering or subsequently editing it you must commit your change with CTRL + Shift + Enter not just Enter.