Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Last five entries

This is a discussion on Last five entries within the Excel Questions forums, part of the Question Forums category; Try... =LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97))&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-1)&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-2)&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-3)&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-4)&""))...

  1. #11
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,080

    Default Re: Last five entries

    Try...

    =LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97))&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-1)&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-2)&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-3)&""))&LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&INDEX($K$28:$K$97,MATCH(BigNum,$K$28:$K$97)-4)&""))
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,717

    Default Re: Last five entries

    Domenic: Have a look at Grove's ACONCAT code, available at this board.
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #13
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,080

    Default Re: Last five entries

    Quote Originally Posted by Aladin Akyurek View Post
    Domenic: Have a look at Grove's ACONCAT code, available at this board.
    Thanks Aladin! Always appreciate your help!

    I've taken a look at the code and found it very interesting. I learned one or two things. So that's great.

    In this instance, though, since the last 5 entries are being sought, I think the following custom function (placed in a regular module) might suffice...

    Code:
    Option Explicit
    Public Function MultiConCatLast5(ByRef rRng As Range, Optional ByVal sDelim As String = "") As String
        Dim i As Long
        Dim j As Long
        j = 0
        For i = rRng.Count To 1 Step -1
            If rRng(i) <> "" Then
                j = j + 1
                MultiConCatLast5 = MultiConCatLast5 & sDelim & rRng(i).Text
            End If
            If j = 5 Then Exit For
        Next i
        MultiConCatLast5 = Mid(MultiConCatLast5, Len(sDelim) + 1)
    End Function
    Then the worksheet formula can simply be...

    =MultiConCatLast5(K28:K97,", ")
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  4. #14
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,419

    Default Re: Last five entries

    Now thats a perfect solution!

    Thanks for your help

  5. #15
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,419

    Default Re: Last five entries

    Hi Domenic,

    I have been using your solution which has been fine, until the small problem just arisen

    The last 5 entries are now at the top of the list (in reverse order)

    Is there anyway to change the code to show this?

    Thanks in advance

  6. #16
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,080

    Default Re: Last five entries

    Quote Originally Posted by ExcelRoy View Post
    Hi Domenic,

    I have been using your solution which has been fine, until the small problem just arisen

    The last 5 entries are now at the top of the list (in reverse order)

    Is there anyway to change the code to show this?

    Thanks in advance
    Try...

    Code:
    Option Explicit
    Public Function MultiConCatLast5(ByRef rRng As Range, Optional ByVal sDelim As String = "") As String
        Dim i As Long
        Dim j As Long
        j = 0
        For i = 1 To rRng.Count
            If rRng(i) <> "" Then
                j = j + 1
                MultiConCatLast5 = MultiConCatLast5 & sDelim & rRng(i).Text
                If j = 5 Then Exit For
            End If
        Next i
        MultiConCatLast5 = Mid(MultiConCatLast5, Len(sDelim) + 1)
    End Function
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  7. #17
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,419

    Default Re: Last five entries

    Thanks Domenic,

    Works a treat, but it is in a range of cells that get hidden/unhidden, and when that happens it resorts to "#VALUE!"

    Is there any way round this as the cells it is calculating from is still there but just hidden?

    Thanks for your help

  8. #18
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,080

    Default Re: Last five entries

    Quote Originally Posted by ExcelRoy View Post
    Thanks Domenic,

    Works a treat, but it is in a range of cells that get hidden/unhidden, and when that happens it resorts to "#VALUE!"

    Is there any way round this as the cells it is calculating from is still there but just hidden?

    Thanks for your help
    I just tried referencing a range of cells that were hidden, and the function returned the desired result. Could the error value be the result of some other issue?
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  9. #19
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,419

    Default Re: Last five entries

    Hi Domenic,

    The formula is on row 28,

    when i use the hidden/unhidden macro it hides rows 9:97, then when i unhide those rows it shows #VALUE!

    not sure if there would be a clash in the code because it was within those ranges ?

  10. #20
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,080

    Default Re: Last five entries

    Can you post the macro that hides/unhides the rows? Also, which cells are being referenced?
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

Page 2 of 3 FirstFirst 123 LastLast

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
  •  


DMCA.com