Condensing a list/range with blank cells to a new list/range without blanks

[KR]

I have a list of cells, which are all dynamically linked to other stuff in
my workbook. The main purpose is that the non-blank cells are "flags" that
show that some task has not been completed. However, I have a long enough
list that it isn't easy to look at, or print, because each cell is dedicated
to one task.

What I'd like to do is set a new range of cells to show only the "non-blank"
cells from the larger range.

I did put all my source cells in one column.

Can anyone give me an easy formula that will check for each subsequent
non-blank cell? I've been playing around with VLookup, but haven't gotten it
working yet. It is also possible that the first (or first several) cells in
my larger range might be blank, so that makes it a bit harder.

Any help or code snippets would be greatly appreciated!

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

[liquidmettle]

Hello Keith,

I have just the solution for you.

First of all, you need to make a named range.

You can do this two ways.

1. Go to Formulas>Name Manager>New

a. Name your new range Blanks1 (for ease)
b. In the little formula bar below type =yourfirstcell:yourlastcell


OR


2. Select the cells in the column corresponding to your data (all of them), click in the box to the left of the formula bar, type Blanks1 and hit enter. If you don't hit enter, it wont name that range.

Okay, now for the formula once the range is created:

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

I originally obtained the base formula from another place I cannot remember. But if you put this into the column next to the one with the blanks, and hit CONTROL+SHIFT+ENTER you can then drag down to produce a column with no blanks but all your other data.

You need to hit control+shift+enter because this is an array formula. If you just hit enter it won't work properly. Voila.

The way this formula is set up, after the last entry #N/A will appear in every cell after. If you would rather the end cells be blank themselves, just replace NA() with "" at the end of the formula.

As far as I have used this formula, I've always started it on row 3 (whatever column you choose). I honestly don't know if thats significant to its function but you should have it level with the column that you are trying to remove the blanks from.

Hope this helps,

Liquidmettle.