Extract first value and last value from a table
 

I have a table of data that has a variable number of columns i.e. week 1 in
column A, week 2 in column B and so on. More data is added as another week
passes. The table has thousands of rows containing an ID number relating to a
store.

Each store in row 1, 2, 3 etc. has a value in some of the columns but does
not have a value in every column.

Can anyone help with a formula that returns the first value and another
formula that returns last value from the weeks of columns?

For example, for store 1 the first value may be in week 5 and the last value
in week 34. Store 2 may have the first value in wk 18 and the last value in
week 46. There may be many values between the first and last value too.

All suggestions gratefully received.

Extract first value and last value from a table
 

Consider row #28:

=INDEX(28:28,MATCH(TRUE,28:28<"",0)) will return the first non-blank value
in the row. This is an array formula that must be entered with
CNTRL-SHFT-ENTER, not just the ENTER key.

=LOOKUP(2,1/(28:280),28:28) will return the last value in the row. This
does not need to be an array formula.
--
Gary''s Student - gsnu200814


"Alan B" wrote:

I have a table of data that has a variable number of columns i.e. week 1 in
column A, week 2 in column B and so on. More data is added as another week
passes. The table has thousands of rows containing an ID number relating to a
store.

Each store in row 1, 2, 3 etc. has a value in some of the columns but does
not have a value in every column.

Can anyone help with a formula that returns the first value and another
formula that returns last value from the weeks of columns?

For example, for store 1 the first value may be in week 5 and the last value
in week 34. Store 2 may have the first value in wk 18 and the last value in
week 46. There may be many values between the first and last value too.

All suggestions gratefully received.