Last five entries

[ExcelRoy]

hi,

hopefully someone can help me out here, im a little stuck for a formula?

i have a range K28:K97 with numbers in each cell, now starts at row 28, but every week or so a new number is added upto a maximum of 97 (sometimes not that many)

what i would like is a formula to show only the last 5 entries

hope someone can help me out here

thanks in advance

 

[Domenic]

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)&""))

 

[Aladin Akyurek]

Domenic: Have a look at Grove's ACONCAT code, available at this board.

 

[Domenic]

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...

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,", ")

 

[ExcelRoy]

Now thats a perfect solution!

Thanks for your help

 

[ExcelRoy]

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

 

[Domenic]

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...

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

 

[ExcelRoy]

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

 

[Domenic]

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?

 

[ExcelRoy]

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 ?

 

[Domenic]

Can you post the macro that hides/unhides the rows? Also, which cells are being referenced?