First first or last cell of data

excelfreaky

New Member
Joined
Feb 22, 2005
Messages
4
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
excelfreaky said:
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
excelfreaky said:
...
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top