Return 5 latest entries for specific column

[xlDude]

I'm working on a project with tables. I'm not sure if I can use a function or I have to vba. I want to retrieve the lastest 5 entries for a specific column (column AA), but not blank ones. Then I want to retrieve latest 5 entries for column AC. On a seperate sheet I want the entries to be seen in order and I want it to change as new data is entered into the table. In the end I'll have the latest 5 entries for Internal (Column AA) and then after that the latest 5 entreis for External (Column AC).

 

[circledchicken]

Hi,

Here is an example set-up that you can adjust to suit your table. These are array formulae so you will need to press CTRL-SHIFT-ENTER rather than just ENTER before dragging down:

The table:

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]AA[/TH]
[TH]AC[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Internal[/TD]
[TD]External[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]a[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD][/TD]
[TD]h[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD][/TD]
[TD]i[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]d[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD][/TD]
[TD]k[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]e[/TD]
[TD]l[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]f[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

The output of the 5 latest:

Sheet2[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Internal[/TD]
[TD]External[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]b[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]c[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]d[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]e[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]f[/TD]
[TD]l[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
A2	=INDEX(Table1[Internal], LARGE( IF(Table1[Internal]<>"",ROW(Table1[Internal])-ROW(INDEX(Table1[Internal],1))+1), 6-ROWS(A$1:A1)))
B2	=INDEX(Table1[External], LARGE( IF(Table1[External]<>"",ROW(Table1[External])-ROW(INDEX(Table1[External],1))+1), 6-ROWS(B$1:B1)))

<tbody>
[TD="bgcolor: #FFFFFF"] Array Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Note also:

• This assumes you are working with smart tables (Excel 2007+). Drag the formula in each column down 5 rows and change the table name as appropriate.
• Also assumes the table is sorted in ascending order by date.
• If you want the list the other way around, just remove the '6-' in each formula.
• Here are some good articles for learning the table structured reference syntax:
  • Working with Tables in Excel 2010 and 2007
  • Using structured references with Excel tables - Excel - Office.com

 

[xlDude]

Thanks, works awesome. Good leason learned.

 

[circledchicken]

Thanks, works awesome. Good leason learned.

You're welcome - thanks for the feedback.