Grouping last 10 by dates

[robbymp]

Hi, I been struggling with this for awhile, I'm trying to group the last 10 records by date for each field, totals only, also if I add another date, I need it to keep a running total by last 10, I can do this in Microsoft Access using Top Select 10. Is this possible in excel or is there a link to an example, I can follow, I tried doing Pivot tables, couldn't get results.

Thank You

 

[mrshl9898]

updating

 

[mrshl9898]

Apologies, try this:

Just update the pivot table number and field name.

Sub ShowLast10Days()
Dim pi As PivotItem
Dim lLoop As Long
Dim pt As PivotTable
Dim lCount As Long
Dim lDays As Long


    lDays = 10


    Application.ScreenUpdating = False
    Set pt = ActiveSheet.PivotTables("PivotTable2")
        
            For Each pi In pt.PivotFields("Date").PivotItems
            On Error Resume Next
                pi.Visible = False
            Next pi
        
        
        With pt.PivotFields("Date")
            For lLoop = .PivotItems.Count To 1 Step -1
                .PivotItems(lLoop).Visible = True
                lCount = lCount + 1
                If lCount = lDays Then Exit For
            Next lLoop
        End With
        
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[Fazza]

Yes, Excel can do a top 10 exactly the same as Access. You can use the same SQL - such as in a query (table), pivot table. Or via VBA additionally with recordset type approaches.

So SQL as in Access, something like,
SELECT TOP 10 fields
FROM source
WHERE critieria
GROUP BY fields if required
ORDER BY somefield DESC