Sorting blank data out of a list

**Virgule** · Oct 14th, 2011, 01:19 PM

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. : For example excel file, follow link to same thread on another forum
http://www.excelforum.com/excel-gene...of-a-list.html

**Excelestial** · Oct 14th, 2011, 01:22 PM

there is a sort formula you can use to sort your data without doing it manually. You can also do a counter to enumerate your non-blank data...which will lead to composing a solid list without blank spaces. Both require naming the range to make it dynamic. Would you like a sample? Or does this make sense to you?

**Virgule** · Oct 14th, 2011, 01:28 PM

I don't want the blanks to be in the list, weither they are "named" otherwise or not. As for the SORT formula, I have excel 2010 in french and I can't find any translation for SORT in french. Seems the SORT function doesn't exist.