# 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))&amp;""))&amp;LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&amp;INDEX(\$K\$28:\$K\$97,MATCH(BigNum,\$K\$28:\$K\$97)-1)&amp;""))&amp;LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&amp;INDEX(\$K\$28:\$K\$97,MATCH(BigNum,\$K\$28:\$K\$97)-2)&amp;""))&amp;LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&amp;INDEX(\$K\$28:\$K\$97,MATCH(BigNum,\$K\$28:\$K\$97)-3)&amp;""))&amp;LOOKUP(REPT("z",255),CHOOSE({1,2},"",", "&amp;INDEX(\$K\$28:\$K\$97,MATCH(BigNum,\$K\$28:\$K\$97)-4)&amp;""))...

1. ## 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)&""))

2. ## Re: Last five entries

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

3. ## Re: Last five entries

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

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

4. ## Re: Last five entries

Now thats a perfect solution!

5. ## 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?

6. ## Re: Last five entries

Originally Posted by 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?

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

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

8. ## Re: Last five entries

Originally Posted by 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?

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?

9. ## 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. ## Re: Last five entries

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

Page 2 of 3 First 123 Last

#### Posting Permissions

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