Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract from a table a value by interpolating Maria New Users to Excel 1 October 14th 08 05:33 PM
how to extract information from one table to another one Omics Excel Discussion (Misc queries) 5 June 13th 08 10:06 PM
Extract Data From A Table zephyr Excel Discussion (Misc queries) 0 November 16th 06 05:43 PM
Filter records in a table to extract to another table gams Excel Discussion (Misc queries) 0 May 9th 06 08:38 PM
Extract a value from a table. JP Arnaud Excel Worksheet Functions 1 February 12th 05 04:33 PM


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"