Domenic: Have a look at Grove's ACONCAT code, available at this board.
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)&""))...
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: 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.
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...
Then the worksheet formula can simply be...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
=MultiConCatLast5(K28:K97,", ")
Now thats a perfect solution!
Thanks for your help
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
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
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 ?
Can you post the macro that hides/unhides the rows? Also, which cells are being referenced?
Like this thread? Share it with others