Sorting blank data out of a list

**Virgule** · 10-14-2011, 01:14 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. Same thread posted on another forum :
http://www.mrexcel.com/forum/showthr...61#post2897961

**JBeaucaire** · 10-14-2011, 03:15 PM

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.

**HSV** · 10-14-2011, 03:36 PM

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

=IFERROR(INDEX($A$2:$A$200,SMALL(IF($A$2:$A$200<>"",ROW($A$2:$A$200)-1,""),ROW()-1)),"")

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

**Virgule** · 10-14-2011, 03:55 PM

Thanks HSV and JBeaucaire,

I've tried JBeaucaire's solution and had some problems with it because I still had the column header has text and not sharing the formula. Also, I had the sumproduct elsewhere than C1. I don't really know why I couldn't make it work without these seemingly arbitrary conditions but it does work once everything begins on the first row.

I have yet to try HSV's solution but I'm sure it'll work as well.

So thanks to both of you for the help.

Now I have a drop down list with all the non blank data grouped at the start of the list. A small issue occurs though. When I open up the drop down list, it shows data from the first blank entry down so the list looks empty. The non blank data is accessed by going up the list. Is there a way of showing the first non blank data as the first element in the list right when I open it ? I want to keep a large source for my data validation list as further components will be added by the user. Maybe that's the source of the problem. Checking the "ignore if blank" box doesn't seem to do anything.