Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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, |
#5
|
|||
|
|||
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 -- |
#6
|
|||
|
|||
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 -- |
#7
|
|||
|
|||
"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 -- |
#8
|
|||
|
|||
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 -- |
#9
|
|||
|
|||
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 -- |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|