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

 

[Corticus]

Hi gazchops,

This user defined function will do it (until Aladin comes along with a non-VBA solution),

Function firstocc(rng As Range)

Dim cl As Range

firstocc = 0#

For Each cl In rng
    If Not IsNull(cl) And cl <> "" Then
        firstocc = cl.Value
        Exit Function
    End If
Next cl

firstocc = cl.Value
 
End Function

Just paste the above code into a standard module, and use the formula firstocc() like any other built-in Excel function.

In your case, it would be like = firstocc(B3:IV3) in row 3

HTH,

 

[Aladin Akyurek]

That formula for the last non-zero numeric value must be...

=INDEX(B3:IV3,,MAX(INDEX((B3:IV3<>0)*(B3:IV3<>"")*COLUMN(B3:IV3),1,0))-COLUMN(B3:IV3)+1)

For the first value (any value)...

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

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

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I
1			cat	8	9		6	5
2
3
4	3
5
6
Sheet3

 

[gaz_chops]

Aladin, I don't know why but when I've pasted your formula it doesn't appear to work correctly.
I have added an extra bit to return the column header based on the ref the formula returns.
I also came up with a version. having tweaked your original formula, but this does not always work either.

Book1
	A	B	C	D	E	F	G	H	I	J
2	Aladin	InstallPd	F2002P03	F2002P04	F2002P05	F2002P06	F2002P07	F2002P08	F2002P09	F2002P10
3	0	F2002P04		28	35	21	28	32	31	28
4	F2002P06	F2002P09							4	21
5	F2002P10	F2002P10								8
6	0	F2002P04		28	35	28	28	40	23	28
7	F2003P10	F2002P11
8	F2003P06	F2002P06				16	28	36	27	28
9	0	F2002P04		28	35	25	31	34	29	28
10	0	F2002P04		30	35	32	28	36	27	28
11	0	F2002P04		28	35	28	28	36	26	21
12	0	F2002P07					34	33	30	29
13	0	F2002P04		28	35	28	28	32	31	28
14	0	F2002P12
15	0	F2002P09		26						16
16	F2002P10	F2002P10								8
Sheet1

 

[Aladin Akyurek]

The formula for the first value should work as intended...

Book13
	A	B	C	D	E	F	G	H	I	J	K
1
2		InstallPd	F2002P03	F2002P04	F2002P05	F2002P06	F2002P07	F2002P08	F2002P09	F2002P10
3	2	F2002P04		28	35	21	28	32	31	28
4	7	F2002P09							4	21
5	8	F2002P10								8
6	2	F2002P04		28	35	28	28	40	23	28
7	#N/A	#N/A
8	4	F2002P06				16	28	36	27	28
9	2	F2002P04		28	35	25	31	34	29	28
10	2	F2002P04		30	35	32	28	36	27	28
11	2	F2002P04		28	35	28	28	36	26	21
12	5	F2002P07					34	33	30	29
13	2	F2002P04		28	35	28	28	32	31	28
14	#N/A	#N/A
15	2	F2002P04		26						16
16	8	F2002P10								8
17
Sheet1

The formula...

=INDEX($C$2:$IV$2,MATCH(1,INDEX(1-ISBLANK(C3:IV3),1,0),0))

does not mirror the conditions incorporated in the formula for the last value. If that should be the case, we can expand the formula in question.

In column A you see the MATCH bit. If that is not necessary, ditch column A. The formula in column B retrieves the header that corresponds to the first value.

 

[gaz_chops]

Aladin, got it, thamks a million.

Garry

 

[P Sitaram]

The following array formula can be entered on the first cell of each row to obtain the first non-blank cell in each row from Column B to Column IV:

{=ADDRESS(ROW($A1),MIN(IF(NOT(ISBLANK($B1:$IV1)),COLUMN($B1:$IV1))))}

 

[mgirvin]

Dear Aladin,

I am reading Mr Excel’s new book and the first formula is of course from you! (That is why I am posting here six years after the last post…)

Your formula is:

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

Then I tried this:

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

Then I tried this one:

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

All three seem to work.

When I look at these parts of the formulas:

INDEX(1-ISBLANK(B3:IV3),1,0) and 1-ISBLANK(B3:IV3) and ,--(B3:IV3<>"") I get the same column array of zeros and ones.

My questions are:

1) Why did you use the INDEX function and a 0 (zero) for the column_num?
2) Is any one of these formula parts faster calculating or more efficient in some way?

 

[Aladin Akyurek]

Dear Aladin,

I am reading Mr Excel’s new book and the first formula is of course from you! (That is why I am posting here six years after the last post…)

Your formula is:

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

Then I tried this:

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

Then I tried this one:

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

All three seem to work.

When I look at these parts of the formulas:

INDEX(1-ISBLANK(B3:IV3),1,0) and 1-ISBLANK(B3:IV3) and ,--(B3:IV3<>"") I get the same column array of zeros and ones.

My questions are:

1) Why did you use the INDEX function and a 0 (zero) for the column_num?
2) Is any one of these formula parts faster calculating or more efficient in some way?

[A]

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



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

or better:

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

All of these requires CSE. These two are not equivalent. With [A], we would get a hit when B3 for example would house a formula blank like ="", with we wouldn't.

I thought I'll invent a non-CSE version (I wouldn't be surprised if somebody else also had the same idea before I did)...

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

This is equivalent to [A]. Since INDEX is also capable to return an array, the inner INDEX call used for that purpose:

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

means: All cells from row 1. The 0 bit specifies all columns
[*]. Not that this is an array of 1/0's. The Match bit looks up 1 in that array.


While this approach avoids CSE, its CSE equivalent [A] should be a bit less expensive.

------------------------

[*] Another example:

=INDEX(A2:B4,0,2)

which means: all cells from column 2, that is, B2, B3, and B4.