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

 

[nightcrawler23]

Till now what i could figure out involves putting formulas in multiple columns...but it works

suppose you have ur data in col C.

 D4 = ISBLANK(C4)

. fill this in colD

E4 = E3+MATCH(FALSE,INDIRECT("D" & (E3+1) & ":D100"),0)

where

 E3 = 0

F4 = INDIRECT("C" & E4)

You will have ur data in col F.


Another suggestion will be to use filters.

 

[bone2663]

Dont really understand
D4 = isblank(C4) ( is this formula in D4) if i do it returns "false"
then E4 formula is that in e4 etc ???

 

[Aladin Akyurek]

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

Control+shift+enter, not just enter, and copy down:

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

 

[bone2663]

ok, working great so far, now after all of the "non blanks" im getting this
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: yellow" align=middle width=64 height=16 x:err="#NUM!">#NUM!</TD></TR></TBODY></TABLE>
do i put something like iserror in front of all of that formula
and thanks HEAPS. I've been trying to get this for ages

 

[Aladin Akyurek]

ok, working great so far, now after all of the "non blanks" im getting this
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 48pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl40 height=16 width=64 align=center x:err="#NUM!">#NUM!</TD></TR></TBODY></TABLE>
do i put something like iserror in front of all of that formula
and thanks HEAPS. I've been trying to get this for ages

If you are on 2007 or later...

=IFERROR(INDEX($C$4:$C$100,SMALL(IF(1-($C$4:$C$100=""),ROW($C$4:$C$100)-ROW($C$4)+1),ROWS($D$1:D1),"")

On all versions...

D1, just enter:

=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]

ok, using excel 2003, which and how formula ?

 

[Aladin Akyurek]

ok, using excel 2003, which and how formula ?

See under "On all versions" in my last reply.

 

[bone2663]

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

 

[mikerickson]

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)