Find the first and last value

D

darkXL

Hi,

I have a list of periods, with assignments starting and ending at different
periods. I need a formula that can search each row, find the first no blank
value and get the corresponding period.

Is there a formula that can do this as my VB understanding is limited.

thx
 
M

Max

One way ..

Assuming the periods are labelled in B2:F2,
with data running down in B3:F3, B4:F4, etc

Put in say, G3, and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER:

=IF(COUNTBLANK(B3:F3)=5,"",
INDEX(B$2:F$2,,MATCH(1,--(B3:F3<>""),0)))

Copy G3 down
 
D

darkXL

Thx Max,

This worked to find the start period, how can this be changed to find the
end period too? Also as some assignements will have work done on period 3 no
work on period 4 and is completed on 5 i need the 5th period returned.

Thanks,
 
M

Max

Extending the example in the earlier response, one way to get the last
period (Note that this assumes data entered is either text or would be in
text format, for e.g.: any numbers entered as data would be preceded with an
apostrophe " ' ")

Put in H3:

=IF(OR(COUNTBLANK(B3:F3)=5,,COUNTA(B3:F3)=1),
"",INDEX(B$2:F$2,MATCH(REPT("Z",255),B3:F3)))

(Normal ENTER will do)

Copy H3 down

Col H will return the last periods for each row
(The previous col G returns the first periods for each row)

The core formula used to get the last period for the row:
INDEX(B$2:F$2,MATCH(REPT("Z",255),B3:F3))
was a direct adaptation from Bob Phillip's excellent page at:
http://www.xldynamic.com/source/xld.LastValue.html#S013
 
M

Max

darkXL said:
... can this be changed though to accept numbers?
Click to expand...

If it's only numbers that'll be entered as data,

Put instead in H3, and array-enter
(press Ctrl+Shift+Enter):

=IF(OR(COUNTBLANK(B3:F3)=5,COUNTA(B3:F3)=1),
"",INDEX(B$2:F$2,1,
MAX(IF(ISNUMBER(B3:F3),COLUMN(B3:F3))-1)))

Copy down
 
M

Max

Maybe it'll be simpler for the error trap parts of both array formulas to
use:

In G3, array-entered:
=IF(COUNTA(B3:F3)=0,"",
INDEX(B$2:F$2,,MATCH(1,--(B3:F3<>""),0)))

In H3, array-entered:
=IF(COUNTA(B3:F3)<=1,"",INDEX(B$2:F$2,1,
MAX(IF(ISNUMBER(B3:F3),COLUMN(B3:F3))-1)))

Select G3:H3 and copy down
 
Top