Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract from a table a value by interpolating | New Users to Excel | |||
how to extract information from one table to another one | Excel Discussion (Misc queries) | |||
Extract Data From A Table | Excel Discussion (Misc queries) | |||
Filter records in a table to extract to another table | Excel Discussion (Misc queries) | |||
Extract a value from a table. | Excel Worksheet Functions |