Find the first and last value
 

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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"darkXL" wrote in message
...
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

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,

"Max" wrote:

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"darkXL" wrote in message
...
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

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"darkXL" wrote in message
...
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,

whoops (an extra comma), formula should read as :

Put in H3:

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

The forumals work great! can this be changed thought to accept numbers?

"Max" wrote:

whoops (an extra comma), formula should read as :

Put in H3:

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"darkXL" wrote:
... can this be changed though to accept numbers?

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks Max, formula's work perfectly, I'll definately use these forum's again

"Max" wrote:

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"darkXL" wrote in message
...
Thanks Max, formulas work perfectly, I'll definitely use these forums
again