Sorting blank data out of a list

Hello,

First, let me say that I know a lot of threads have been done on this subject. I have been reviewing about 20 of them for the past two days and haven't found a proper solution to my problem. Well I found something very close but couldn't manage to make the formula work for my case or understand it well enough to write my own. I really need your wisdom here.

I have a list of components interlaced with blank cells. I wanted this list to be the source of a drop down list but since I have a lot of blank data, the drop down list would have been painful to use. So from what I read, the best way of doing it would be to sort out the blank data out off the source first.

I could do this with a simple filter on my column and unchecking "blanks". The problem with that is that this needs to be redone in order to refresh. I want the blank free list to update itself as soon as another component is entered. Same problem goes with sorting (AZ icon) the column to get blanks at the end.

I would need the column besides my blank filled list to be a compact form of the list; blank free. I would really like a non VBA solution as I am a TOTAL NOOB with VBA. However, I always end up against the same wall : I can't loop with formulas.

If you have any ideas, let me know.

Thanks in advance.

N.B. Same thread posted on another forum :
http://www.mrexcel.com/forum/showthr...61#post2897961

the formula in B1 is an Array formula, any changes, confirm the formula by pressing CTRL-SHIFT-ENTER to reactivate the array, then copy down.

C1: =SUMPRODUCT(--($A$1:$A$100<>""))
B1: =IF(ROW(A1)>$C$1, "", INDEX($A$1:$A$100, SMALL(IF($A$1:$A$100<>"", ROW($A$1:$A$100)), ROW(A1))))

...array entered and copied down.

Only for Excel 2007 or later.
In B2;
Arrayformula.

Please Login or Register  to view this content.

Insertion with Ctrl+Shift+Enter → not only Enter.
Fill down as far as necessary.

Something like attachment?