CODEWITHSUNDEEP
excelexcel-formulavba
rvphx
rvphx

Reputation: 2402

SELECT (DISTINCT COUNT) in Excel

I have done this without much effort on T-SQL, but I want to implement the same on Excel VBA. How to achieve this? I have a field on my sheet called "Stops" which pertains to the Stops that a truck makes while delivering its order. I want to count the total number of stops. 1 stop can have multiple orders to be delivered. The data on the stops column is something like:

Order#  Stops
1527305 1
1527305 1
1529418 2
1529418 2
1527299 3
1527299 3
1528894 5
1528894 5
1529529 6
1529529 6
1529518 7
1529518 7
1527522 8
1527522 8

So, the final count should be just 7 unique stops. The current code looks at the last row and takes it as the total stops (which is wrong). The code that I have right now is as follows:

    ActiveCell.Offset(0, 7).Select  ' H = stop number
    Selection.Value = curStop 'sets stop number
        If Selection.Value = 0 Then
            ActiveCell.Offset(-1, 0).Select
            curStop = ActiveCell.Value
            ActiveCell.Offset(1, 0).Select
            Selection.Value = curStop
        End If

I have done something on T-SQL with one line of code, but dont know how to do this on Excel. Any help will be appreciated. Thanks!!

Upvotes: 0

Views: 11369

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169264

You already have a formula-based solution, and that may be what you wish to use in this case.
If you are curious about a way to solve the problem using VBA, read on.

For the problem of identifying unique values a dictionary can be an appropriate data-structure.
In particular, you may check for a key's existence before adding a key.
Tested example follows:

(Please note: you must enable (check the box) Tools > References > "Microsoft Scripting Runtime".) 

Option Explicit

Function uniq_dict(ByRef row As Long, ByRef col As Long)
    Dim dict As New Scripting.Dictionary
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1")

    For row = 1 To ws.Cells(Rows.Count, col).End(xlUp).row
        If Not dict.Exists(ws.Cells(row, col).Value) Then
            dict.Add ws.Cells(row, col).Value, Null '# can insert something here 
        End If
    Next row

    Set uniq_dict = dict
End Function

Sub call_uniq_dict()
    Dim i As Integer
    Dim k() As Variant
    Dim dict As New Scripting.Dictionary
    Set dict = uniq_dict(1, 1)

    Debug.Print "total items in dict:", dict.Count
    k = dict.Keys
    For i = 0 To dict.Count - 1
        Debug.Print "  dict key:", k(i)
    Next
End Sub

Result:

total items in dict:         5 
  dict key:    1 
  dict key:    2 
  dict key:    3 
  dict key:    7 
  dict key:    8

Upvotes: 1

playercharlie
playercharlie

Reputation: 629

You can also try

=SUM(IF($B$2:$B$15,1/COUNTIF($B$2:$B$15,$B$2:$B$15)))

as an array formula solution, assuming your data is in B2:B15. Array formulae are, of course, entered with a Ctrl + Shift + Enter, rather than an enter, so maybe your solution is slightly better :)

Also shouldn't your question say the answer is 7 stops, and not 5???

Upvotes: 1

Related Questions