Nth non blank cell

[bone2663]

I am trying to find a formula that will return the nth non blank cell in an array (C4:c100)
In cells c4 to c19 is formula that will return "" then in c20 formula returns 0430-0830.then c21 is formula that returns "" and so on.
I am wanting to get all of the non "" cells.
eg
d1= first non "" in array c4:c100
d2= 2nd non "" in array c4:c100
etc

help please

 

[Aladin Akyurek]

get an error posted to many arguements and the "" highlited when ctrl shift enter

D1:

=SUMPRODUCT(1-($C$4:$C$100=""))

D2, control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$2:D2)<=$D$1,INDEX($C$4:$C$100,SMALL(IF(1-($C$4:$C$100=""),ROW($C$4:$C$100)-ROW($C$4)+1),ROWS($D$2:D2),"")

 

[bone2663]

This CSE formula will return the first non blank cell in C4:C100
=INDEX(C:C,SMALL(ROW($C$4:$C$100)+(100*($C$4:$C$100="")), 1))&""

This will return the second
=INDEX(C:C,SMALL(ROW($C$4:$C$100)+(100*($C$4:$C$100="")), 2))&""

This could be entered into a cell and dragged down
=INDEX(C:C,SMALL(ROW($C$4:$C$100)+(100*($C$4:$C$100="")), ROW(A1)))&""

All these formulas should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

3rd one worked fantastic, thanx heaps all

 

[Aladin Akyurek]

3rd one worked fantastic, thanx heaps all

Insert a few rows for the formula cell and see whether ti still works.

By the way, Post #11 works as intended and it's robust...

 

[Aladin Akyurek]

d1:

=sumproduct(1-($c$4:$c$100=""))

d2, control+shift+enter, not just enter, and copy down:

=if(rows($d$2:d2)<=$d$1,index($c$4:$c$100,small(if(1-($c$4:$c$100=""),row($c$4:$c$100)-row($c$4)+1),rows($d$2:d2),"")

d2:

=if(rows($d$2:d2)<=$d$1,index($c$4:$c$100,small(if(1-($c$4:$c$100=""),row($c$4:$c$100)-row($c$4)+1),rows($d$2:d2))),"")