Here’s the made up scenario (that resembles a real one, kind of): I have 200 companies with their city, state, and a sales figure. I want to find the companies whose sales are near my focus company, but only if they’re in the same state.
In Excel’s UI, the steps would be 1) Sort the list 2) Autofilter on state 3) Find my company and look whose above and below.
If I care about Monarch Playing Cards, and I want to know the three closest companies with less sales and the three with more sales, I can see that in the above list. And I can see that there are only two with more.
In code, the steps are pretty much same, but I filter before I sort. Here’s the code.
Dim clsCompanies As CCompanies
Dim clsWisky As CCompanies
Dim vaOutput As Variant
Dim clsFocus As CCompany
‘Fill all companies
Set clsCompanies = New CCompanies
clsCompanies.Fill Sheet1.Range(“A2:D201”)
‘identify the one I care about
Set clsFocus = clsCompanies.FindByName(“Monarch”)
‘get only those companies in the same state
Set clsWisky = clsCompanies.FilterByState(clsFocus.State)
‘sort them by sales
clsWisky.SortBySales
‘get an array of the 3 below and 3 above
vaOutput = clsWisky.WriteComparables(clsFocus, 3)
‘write it to a range
Sheet1.Range(“G1”).Resize(UBound(vaOutput, 1), UBound(vaOutput, 2)).Value = vaOutput
End Sub
Over the next few posts, I’ll go through each step in this code. For this post, I’ll look at setting up the data and the classes.
The first step is to generate some sample data using Quick Sample. I generated 200 entries with FakeCompanyName, US Cities, US States, and Sales. Sales is a new sample I created that looks like this
From $500k to $5m in sales with two decimals. If you create your own sample data, don’t make that upper sales figure too high (there’s a bug I need to fix).
Next, I create a class module called CCompany and put this code in it.
Public CompanyID As Long
Public CompanyName As String
Public City As String
Public State As String
Public Sales As Double
Using my VBHelpers Add-in, I convert those Public Properties to Property Get/Let statements.
Private msCompanyName As String
Private msCity As String
Private msState As String
Private mdSales As Double
Public Property Get Sales() As Double: Sales = mdSales: End Property
Public Property Let Sales(ByVal dSales As Double): mdSales = dSales: End Property
Public Property Get State() As String: State = msState: End Property
Public Property Let State(ByVal sState As String): msState = sState: End Property
Public Property Get City() As String: City = msCity: End Property
Public Property Let City(ByVal sCity As String): msCity = sCity: End Property
Public Property Get CompanyName() As String: CompanyName = msCompanyName: End Property
Public Property Let CompanyName(ByVal sCompanyName As String): msCompanyName = sCompanyName: End Property
Public Property Get CompanyID() As Long: CompanyID = mlCompanyID: End Property
Public Property Let CompanyID(ByVal lCompanyID As Long): mlCompanyID = lCompanyID: End Property
Back to VBHelpers to create a parent class module, CCompanies. Here’s the code that VBHelpers generates for that module:
Private mcolCompanies As Collection
Private Sub Class_Initialize()
Set mcolCompanies = New Collection
End Sub
Private Sub Class_Terminate()
Set mcolCompanies = Nothing
End Sub
Public Property Get NewEnum() As IUnknown
Set NewEnum = mcolCompanies.[_NewEnum]
End Property
Public Sub Add(clsCompany As CCompany)
If clsCompany.CompanyID = 0 Then
clsCompany.CompanyID = Me.Count + 1
End If
Set clsCompany.Parent = Me
mcolCompanies.Add clsCompany, CStr(clsCompany.CompanyID)
End Sub
Public Property Get Company(vItem As Variant) As CCompany
Set Company = mcolCompanies.Item(vItem)
End Property
Public Property Get Count() As Long
Count = mcolCompanies.Count
End Property
It also does other stuff as described in the above linked post, like give me For Each and default property capabilities.
Tomorrow, we’ll look at the Fill method for CCompanies and the FindByName property.
You can download SortFilterClass.zip
I would love to be able to download the example files :)
Good point Steve. Added link.
Why not use a formula in the column to the right that ranks using an array function. eg
In Column E enter:
{=COUNT(IF($C$2:$C$2000=$C2,IF($D$2:$D$2000<$D2,$D$2:$D$2000)))+1}
Then combine this value with column C into Column F: =C2&E2
You can then build a separate table where you select the Company you want from a dropdown list
Then use the rank you get in column C to identify the ranks below and above required.
Combine these with the State and then use match to selct those companies with the otehr values being identified using vlookup.
I think this is much simpler than lots of code in this case. You could also write this approach in code quite easily