I've created a custom function that should satisfy your requirements, therefore avoiding complicated design & maintenance of multiple nested cube functions needed for multi-dimensional reporting of your vehicle sales data.
A further advantage is that using this method, multiple variations can be easily created & edited to provide additional functionality for future reporting needs.
Usage is similar to Excel's built-in Rank function:
- Call the function from a worksheet, specifying Month and/or Category, as well as the ranked result "rank number" that you need to have returned.
For example, to get the Top (#1) selling Truck for January, you could use formula:
=GetTopSeller ( "January", "Trucks", 1 )
or, to get the 10th best selling car for the month listed in cell A1, you could use formula:
=GetTopSeller ( A$1, "Cars", 10 )
The image below shows Syntax & Usage, as well as the sample dataset used in testing the function, and the example output based on the sample data*.
Option Explicit
'Written by ashleedawg@outlook.com for https://stackoverflow.com/q/47213812
Const returnForNoValue = "" 'could be Null, 0, "(Not Found)", etc
Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
'Additional features which can be easily added on request if needed:
' add constants to specify Revenue or Product ID
' allow annual reporting
' allow list of vehicle types, months, etc
' make case insensitive
Public Function GetTopSeller(sMonth As String, sCategory As String, _
sMonthRank As Integer) As Variant() '1=ProductID 2=Revenue
Dim retData(1 To 2) As Variant
strSQL = "Select Month, Category, [Product #], Revenue from [dataTable$] " & _
"WHERE [Month]='" & sMonth & "' AND [Category]='" & sCategory & "' _
Order by [Revenue] DESC"
' close Excel Table DB _before_ opening
If rs.State = adStateOpen Then rs.Close
rs.CursorLocation = adUseClient
' open Excel Table as DB
If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver " &
"(*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
' find appropriate data
With rs
.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
.MoveFirst
If (.RecordCount <= 0) Or (.RecordCount < sMonthRank) _
Or (sMonthRank = 0) Then GoTo queryError
'move the Nth item in list
.Move (sMonthRank - 1)
retData(1) = ![Product #]
retData(2) = !Revenue
End With
'return value to the user or cell
GetTopSeller = retData
Exit Function
queryError:
'error trapped, return no values
retData(1) = returnForNoValue
retData(2) = returnForNoValue
GetTopSeller = retData
End Function
Below are instructions for copying the function into your workbook, thus making it accessible as a worksheet function. Alternatively, the sample workbook could be saved as an Add-on, and then accessed from any workbook by creating a reference to the Add-on.
How to add the VBA function to your workbook:
Select the VBA Code below, and hit Ctrl+C to copy.
In your Excel workbook, and hit Alt+F11 to open the VBA Editor (aka VBE).
Click the Insert menu in the VBE, and choose Module.
Hit Ctrl+V to paste the code.
Click the Debug menu in the VBE, and choose **Compile Project*.
This checks the code for errors. Ideally "nothing" will happen, meaning it's error-free & good to go.
Close the VBE window by clicking the the " ✘ " in the top-right corner of the VBE.
Save the workbook. Your new function is now ready to use!
Usage of the function should be self-explanatory, but do not hesitate to contact me if you need a change, experience a problem, or have any questions.
Good Luck!
