Formula to Extract a List Excluding Blanks

=iferror(index($a:$a,small(index(isblank($a$2:$a$10)*10^10+row($a$2:$a$10),0),row(a1))),"")
try this non array formula and copy towards down

Originally Posted by chotosiete

I Need help with understanding how Array's Work. For some reason I can't grab a list and have it populate without the blanks in between.

I am Trying to use and index and Small, but I am not sure if by pressing Ctrl+Shift+Enter-- or if I am missing something.

Formula I am trying to use is:


=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""),ROW(A1))),"")


I don't know what I am doing wrong.

Thanks for helping!

Your formula is also working well with the cells having text
it is excluding blank cells as well as numbers having cells
you used istext formula that's why it was happened make little changes to this like
=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISBLANK($A$2:$A$10),"",ROW($A$1:$A$9)),ROW(A1))),"")

Hi chotosiete,

Please see attached file with formula in column G


or this

=IFERROR(INDEX($K$2:$K$43,SMALL(IF($J$2:$J$43=$B$2,ROW($J$2:$J$43)-ROW($A$2)+1),ROWS(A$1:A1))),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Regular formula, no Ctrl+Shift+Enter needed

=IFERROR(INDEX($K:$K,AGGREGATE(15,6,ROW($J$2:$J$43)/($J$2:$J$43=$B$2),ROWS(K$1:K1))),"")

E2=IFERROR(INDEX($D:$D,SMALL(INDEX(($D$2:$D$52="")*10^10+ROW($D$2:$D$52),0),ROW(D1))),"") try this and copy towards down

LIST With NO Blanks.xlsm


take a look at this one....it might help it is a macro that someone on here taught me i made it into a button for ease of use

erase the data from column E and try it out works like a charm and is quite simple to adjust yourself if needed

It is also working with the formula
see the attached file
Our ultimate goal is getting the result
it is your choice, whether you want to with macro are with formula
if you get the solution please mark the thread as solved

I have poor comunication skills try to understand the below

IFERROR(INDEX($D:$D,SMALL(INDEX(($D$2:$D$52="")*10^10+ROW($D$2:$D$52),0),ROW(D1))),"")
for suppose $D$2:$D$52 have 10 Blanks cells
if the cell is blank the value will be 10000000000+that row number
if the cell is nonblank the value will be 0+that row number
for suppose $D$2:$D$52 have 10 Blanks cells
upto small 41 it will give row number
after that from small 42 on wards small 42 will give 10000000000+that row number
the available rows (1048576) in excel is less than that
then it gives an error, we given iferror function then it will give "" result

Originally Posted by Betta-J

I'm not sure if this was covered but, what if your range cells have values that were derived with formulas instead of them being typed in to any of the cells in that range.. I'm having difficulty with this formula. The value is in the cell as it shows, but that value came from the formula that is in that cell. Please advise. Thx!

Hi, welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.