The Return non-blank rows formula stops working after 100 rows.

[krii]

Hello.

I'm trying to use the return non blank cells formula you get if you google to return only populated cells from a single column.

{=IFERROR(INDEX(Y$1:Y100;SMALL(IF(LEN(Y$1:Y100)>1;ROW(Y$1:Y100);"");ROW(A1)));"")}

This one works for 148 cells than does not return anything.

If i try to up the range to

{=IFERROR(INDEX(Y$1:Y1000;SMALL(IF(LEN(Y$1:Y1000)>1;ROW(Y$1:Y1000);"");ROW(A1)));"")}

It doesn't return anything at all.

Seems to be hitting some sort of a limit in excel I don't know about.

How do I fix this?

 

[Legacy 143009]

Could you please try the formula below:

{=IFERROR(INDEX(Y$1:Y$1000;SMALL(IF(LEN(Y$1:Y$1000)>1;ROW(Y$1:Y$1000));ROWS(A$1:A1)));"")}

 

[Peter_SSs]

.. the return non blank cells formula you get if you google to return only populated cells from a single column.

{=IFERROR(INDEX(Y$1:Y100;SMALL(IF(LEN(Y$1:Y100)>1;ROW(Y$1:Y100);"");ROW(A1)));"")}

I don't think that was a good Google search result to look at if you are trying to return non-blanks. As well as eliminating blank cells, that also eliminates cells with a single character see column A below.
It is also not robust in that if you subsequently insert any new rows at the top of the sheet it will return more incorrect results - see my second mini sheet below.
I would suggest the formula shown in column B below as it addresses both of the issues mentioned above and also does not require the Ctrl+Shift+Enter confirmation.
Note that my formulas use comma separators so you would need to change to semicolons based on your original formula above.

22 11 29.xlsm
	A	B																						X	Y
1	bb	a																							a
2	gh	bb																							bb
3	jj	gh																							gh
4	kjk	jj
5	1236	k																							jj
6		kjk																							k
7		1236
8																									kjk
9																									1236
Non blanks

Cell Formulas
Range	Formula
A1:A9	A1	=IFERROR(INDEX(Y$1:Y100,SMALL(IF(LEN(Y$1:Y100)>1,ROW(Y$1:Y100),""),ROW(A1))),"")
B1:B9	B1	=IFERROR(INDEX(Y:Y,AGGREGATE(15,6,ROW(Y$1:Y$1000)/(Y$1:Y$1000<>""),ROWS(B$1:B1))),"")

See what happens to the original formula results if a new row is added at the top:

22 11 29.xlsm
	A	B																						X	Y
1
2	0	a																							a
3	k	bb																							bb
4	1236	gh																							gh
5	0	jj
6		k																							jj
7		kjk																							k
8		1236
9																									kjk
10																									1236
Non blanks

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR(INDEX(Y$2:Y101,SMALL(IF(LEN(Y$2:Y101)>1,ROW(Y$2:Y101),""),ROW(A2))),"")
B2:B10	B2	=IFERROR(INDEX(Y:Y,AGGREGATE(15,6,ROW(Y$2:Y$1001)/(Y$2:Y$1001<>""),ROWS(B$2:B2))),"")

 

[krii]

Could you please try the formula below:

{=IFERROR(INDEX(Y$1:Y$1000;SMALL(IF(LEN(Y$1:Y$1000)>1;ROW(Y$1:Y$1000));ROWS(A$1:A1)));"")}

This doesn't return anything at all.

I don't think that was a good Google search result to look at if you are trying to return non-blanks. As well as eliminating blank cells, that also eliminates cells with a single character...

Yes this was my edit to it. As the range i'm running it on doesn't actually contain any empty cells. It's populated by a formula result that outputs "", which for the original version of it :
{=IFERROR(INDEX(A1:A5,SMALL(IF(NOT(ISBLANK(A1:A5)),ROW(A1:A5),""),ROW(A1))),"")}
didn't handle correctly.

I'll try your aggregate formula and see if it works. As it looks like it should handle it correctly

 

[krii]

Yep this works a lot better.

=IFERROR(INDEX(Y:Y,AGGREGATE(15,6,ROW(Y$1:Y$1000)/(Y$1:Y$1000<>""),ROWS(B$1:B1))),"")

Thanks!

 

[Peter_SSs]

Yep this works a lot better.

=IFERROR(INDEX(Y:Y,AGGREGATE(15,6,ROW(Y$1:Y$1000)/(Y$1:Y$1000<>""),ROWS(B$1:B1))),"")

Thanks!

You're welcome. Thanks for the confirmation.