1st occurance of non blank cell

[gaz_chops]

I have data across rows in which I want to find the 1st occurance of a non blank cell and return this as a reference.
The formula in col A was kindly provided by Aladin, this finds the last entry, can this be tweaked? I'll be honest and say that I dont't quite understand how it works.

Row 5 should return 2
Row 9 should return 29

Book2
	A	B	C	D	E	F	G	H	I	J	K
2		F2000P07	F2000P08	F2000P09	F2000P10	F2000P11	F2000P12	F2001P01	F2001P02	F2001P03	F2001P04
3	41						2	29	41
4	28									29	28
5	9			33	9
6	23				19	35	29	27	35	33	23
Sheet1

 

[mgirvin]

Dear Aladin,

I love learning useful Excel details like this from you:

1) =INDEX(B3:IV3,MATCH(TRUE,B3:IV3<>"",0)) ===> even more efficient

2) 1-ISBLANK(B3:IV3) gets a hit from the formula =””

3) =INDEX(B3:IV3,MATCH(1,INDEX(1-ISBLANK(B3:IV3),1,0),0)) is not an array (very clever formula)

I have another question:

I thought that the 0 (zero) for the column_num argument told the INDEX function to return just a row. You said that it “specifies all columns”. “Return just a row” and “specifies all columns” are just different ways to say the same thing, right?

Thank you, Aladin!

 

[Aladin Akyurek]

Dear Aladin,

I love learning useful Excel details like this from you:

1) =INDEX(B3:IV3,MATCH(TRUE,B3:IV3<>"",0)) ===> even more efficient

2) 1-ISBLANK(B3:IV3) gets a hit from the formula =””

3) =INDEX(B3:IV3,MATCH(1,INDEX(1-ISBLANK(B3:IV3),1,0),0)) is not an array (very clever formula)

I have another question:

I thought that the 0 (zero) for the column_num argument told the INDEX function to return just a row. You said that it “specifies all columns”. “Return just a row” and “specifies all columns” are just different ways to say the same thing, right?

Thank you, Aladin!

=INDEX(A2:C6,1,0)

==> all columns (all cells of the horizontal subrange at row 1, that is, A2..C2)

=INDEX(A2:C6,0,2}

==> all rows (all cells of the vertical subrange at column 2, that is, B2..B6.

 

[mgirvin]

Dear Aladin,

For this formula, =INDEX(A2:C6,1,0), “all columns” is easier to understand than the Excel help when they say “entire row”. “All columns” is more explicit.

Thanks!

 

[mgirvin]

Dear Aladin,

I have two questions about this amazing formula.

I have been using the formula “First-Non_Blank” in a number a sample data databases, and I have combined two of your formulas into one:

Formula 1:

=INDEX(B3:IV3,MATCH(TRUE,B3:IV3<>"",0))

This is good because it does not get a hit when a cell contains the formulas =””


Formula 2:

=INDEX(B3:IV3,MATCH(1,INDEX(1-ISBLANK(B3:IV3),1,0),0))

This is good because it does not require Ctrl + Shift + Enter.


Combined Formula 3:

=INDEX(B3:IV3,MATCH(TRUE,INDEX(B3:IV3<>"",1,0),0))

Question 1:
If you did not want to use Ctrl + Shift + Enter, would these be the advantages:

1) No hit for =””
2) Faster calculating than the 1-ISBLANK non-array version (=INDEX(B3:IV3,MATCH(1,INDEX(1-ISBLANK(B3:IV3),1,0),0)))

Question 2):

I timed these two formulas (7 times each):

A] =INDEX(B3:IV3,MATCH(TRUE,INDEX(B3:IV3<>"",1,0),0))

And

B] { =INDEX(B3:IV3,MATCH(TRUE,B3:IV3<>"",0))}

A] was faster. How could this be when I see Formula Evaluator taking more steps for A] than for B]?

What do you think?

 

[Danny_1952]

Last occurance of non blank cell

Hi,

And what could we do to find the last occurance of a non blank cell? The content should be alphanumerical.

Danielle

 

[Aladin Akyurek]

Re: Last occurance of non blank cell

Hi,

And what could we do to find the last occurance of a non blank cell? The content should be alphanumerical.

Danielle

=LOOKUP(REPT("z",255),Reference)

returns the last text (alphanumeric) value, even if that's a formula blank.

Reference can be a definite range like A2:A100 or indefinite like A:A.

BTW, for a definite range....

=LOOKUP(9.99999999999999E+307,SEARCH("?",$A$2:$A$15),$A$2:$A$15)

would avoid returning a formula blank as the last text value.

 

[mgirvin]

Dear Aladin,

Thanks as always for those efficient formulas!

I have a question:

Why 255 and not 2^15?

 

[mgirvin]

Actually... I have two questions:

1) Since 2^15 is one bigger than the max number of characters in a cell, why did you use 9.99999999999999E+307 is this formula:

=LOOKUP(9.99999999999999E+307,SEARCH("?",$A$2:$A$15),$A$2:$A$15)

instead of 2^15?

2) Why 255 in this formula:

=LOOKUP(REPT("z",255),Reference)

 

[mgirvin]

OK, I got the 255 from help! It is the max characters for lookup_value. (I should have already known that...)

But for this formula:

=LOOKUP(9.99999999999999E+307,SEARCH("?",$A$2:$A$15),$A$2:$A$15)

Can you tell me why the 9.99999999999999E+307?

Wouldn't this work:

=LOOKUP(2,SEARCH("?",$A$2:$A$15),$A$2:$A$15)

Because the "?" is a single character and thus the SEARCH("?",$A$2:$A$15) will deliver an array of 1 values and #VALUE! errors?

Or is there some hidden cool reason for the BigNum?

 

[Aladin Akyurek]

OK, I got the 255 from help! It is the max characters for lookup_value. (I should have already known that...)

But for this formula:

=LOOKUP(9.99999999999999E+307,SEARCH("?",$A$2:$A$15),$A$2:$A$15)

Can you tell me why the 9.99999999999999E+307?

Wouldn't this work:

=LOOKUP(2,SEARCH("?",$A$2:$A$15),$A$2:$A$15)

Because the "?" is a single character and thus the SEARCH("?",$A$2:$A$15) will deliver an array of 1 values and #VALUE! errors?

Or is there some hidden cool reason for the BigNum?

We can get the last numeric value from a (one-dimensional) reference with

LOOKUP(MAX(Reference)+delta,Reference)
MATCH(MAX(Reference)+delta,Reference)

where delta is a extremely small number.

However, the MAX(Reference) call means (a) less speed and (b) inconvenience as in MAX(SEARCH("?",A2:A4)). So a sufficiently large number that is unlikely to occur in references of interest would allow us to satisfy (a) and (b). Such a number is available in Excel itself as specified in Help:

<TABLE class=ind><TBODY><TR><TD>Largest number allowed to be typed into a cell</TD><TD>9.99999999999999E+307</TD></TR></TBODY></TABLE>

Hence this BigNum in:

LOOKUP(9.99999999999999E+307,Reference)
MATCH(9.99999999999999E+307,Reference)

as also in:

=LOOKUP(9.99999999999999E+307,SEARCH("?",$A$2:$A$15),$A$2:$A$15)

allowing us to avoid introducing zillions of arbitrary variations.

One exception has formed itself when Harlan Grove came up with:

LOOKUP(N,1/(...),...)

he used 2 for N (instead of 9.99999999999999E+307), a choice, I guess, which emphasizes the fact that 1/(...) cannot be greater than 2.