Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: select the most recent value from one block of 10 columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Location
    Amsterdam
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default select the most recent value from one block of 10 columns

    Hi everybody,

    I have one block of 10 columns. The 10 columns contain data ordered by year (from the most recent to the oldest - 2019 - 2018 .... - 2010).

    Example of block of 10 columns:

    company A 2019 A 2018 A 2017 A 2016 A 2015 A 2014 A 2013 A 2012 A 2011 A 2010 value for company
    X 52 23 256 52
    Y 111 111
    Z 12 12

    What i need is to find the most recent "value for each company" and visualized in the last column.

    Thanks for the help!
    Amanda

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,406
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    Hi Amanda,

    In cell L2 you can test following array formula

    Code:
    =OFFSET(A2,0,MATCH(TRUE,B2:K2<>"",0))
    Hope this will help

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    How about
    =INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))

    This needs to be confirmed with Ctrl Shift Enter, not just Enter
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    Alternatively a non-array formula
    =INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jan 2014
    Location
    Amsterdam
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    Code:
    =OFFSET(A2,0,MATCH(TRUE,B2:K2<>"",0))
    Hi and thanks for helping!
    i tried your formula, but it didn't work.
    I have to find the most recent data available along the row B2 to M2 (where in B2 there is data for 2019 and in M2 the data refers to 2010).

    cheers

  6. #6
    Board Regular
    Join Date
    Jan 2014
    Location
    Amsterdam
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    hi and thanks for helping to you too!

    i tried also both formula but the first one doesn't give any result and the second one gives back the data in column 2019. It seams like it doesn't select from the row.

    cheers,
    Amanda

  7. #7
    Board Regular
    Join Date
    Jan 2014
    Location
    Amsterdam
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    Quote Originally Posted by Fluff View Post
    Alternatively a non-array formula
    =INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))
    Hi Fluff,
    hiand thanks for helping to you too!

    i tried also both formula but the first one doesn't give any result and the second one gives back the data in column 2019. It seams like it doesn't select from the row.

    cheers,
    Amanda

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    In you example there wasn't any data in col 2019, but I thought that that was what you wanted.

    ABCDEFGHIJKLM
    1companyA 2019A 2018A 2017A 2016A 2015A 2014A 2013A 2012A 2011A 2010value for company
    2X52232565252
    3Y221112222
    4Z121212

    Data



    Worksheet Formulas
    CellFormula
    M2=INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))

    Array Formulas
    CellFormula
    L2{=INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,406
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    Hello,

    For an Array Formula ... intead of using the ' standard ' Enter key ...

    you need to use simultaneously the three keys : Control Shift Enter

    Hope this clarifies
    Last edited by James006; Aug 28th, 2019 at 10:58 AM.

  10. #10
    Board Regular
    Join Date
    Jan 2014
    Location
    Amsterdam
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: select the most recent value from one block of 10 columns

    thanks for your feedbacks, James006 and Fluff, but i still dont understand why it does not work..

    here a print screen of what i have done.

    thanks,
    Amanda


    PS: unfortunately the web site does not allow to insert pic from a desktop directory
    Last edited by AmandaSS; Aug 29th, 2019 at 05:46 AM. Reason: bad visualization of the picture

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •