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.
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.
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
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.
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.