First first or last cell of data

[excelfreaky]

Hey,

I tried to find a solution in the other topics without any luck.

I have two columns, A5:a162 is filled with dates and b5:b162 is filled with corresponding amounts (or blanks). I would like to find the date of the first amount (the first non-blank cell in column b) and the date of the last amount (the last non-blank columnn in column b).

I think this can be done in vba, but could it be done in a formula? Thanks for the help, have really been racking my brain to find an elegant solution.

 

[Aladin Akyurek]

Hey,

I tried to find a solution in the other topics without any luck.

I have two columns, A5:a162 is filled with dates and b5:b162 is filled with corresponding amounts (or blanks). I would like to find the date of the first amount (the first non-blank cell in column b) and the date of the last amount (the last non-blank columnn in column b).

I think this can be done in vba, but could it be done in a formula? Thanks for the help, have really been racking my brain to find an elegant solution.

a]

=INDEX(A5:A162,MATCH(TRUE,ISNUMBER(B5:B162),0))

which must be confirmed with control+shift+enter instead of the usual enter.

b]

=LOOKUP(9.99999999999999E+307,B5:B162,A5:A162)

which just needs enter.

 

[excelfreaky]

Wow. Thank you.

Could you explain how these whenever you have a chance?

Thanks

 

[Aladin Akyurek]

...
Could you explain how these whenever you have a chance?...

The ISBLANK bit in

=INDEX(A5:A162,MATCH(TRUE,ISNUMBER(B5:B162),0))

returns a calculated array of TRUE's and FALSE's, that is, something like...

{FALSE,FALSE,TRUE,TRUE,...}

MATCH is matching the lookup value of TRUE against that calculated array with the match-type is set to 0 (in order to effect an exact match). That gives always the position of the first match if successful. For the foregoing calculated array, that would be 3. INDEX, handed over a position value, will return the value at that position in the range it is given. That is, the value at position 3 in A5:A162.

The latter formula,

=LOOKUP(9.99999999999999E+307,B5:B162,A5:A162)

by now pretty much well-known, returns the value from A5:A162 that corresponds to the last numeric value in the range B5:B162. There is much to find about it on this site and MS newsgroups.

 

[excelfreaky]

Thanks

Thanks.