Home > General Tech > Helpful Excel function – Remove blank values from a list

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.
Excel Problem Result

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
Excel Problem First Attempt

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).
Excel Problem Temporary List

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

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.

Advertisements
Categories: General Tech Tags: ,
  1. September 9, 2015 at 8:04 pm

    Thanks for this – it was really useful

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: