vba how do not count rows (or count only once) if they have the same parameter

Question

I need to count rows in column "rating_status2" with value "unclear" and if they have the same "customer_nbr" only once. How it can be done? The structure of the data is following:

rating_status2   customer_nbr
unclear            1         
unclear            2
approved           3
approved           4
unclear            1
unclear            1
unclear            2

Here number of row with value "unclear" should be calculated as 2, because of the same "customer_nbr"

My code is below, but it only works for counting cells which are "unclear". How can I modified it? `

Function Unclear_Rating()
Dim i As Integer
Dim intCounter(1) As Integer
Dim intLastRow As Integer
Dim zelle2 As Range
Dim posMonitoring2 As Integer
Dim zelle3 As Range
Dim posMonitoring3 As Integer

With Sheets("ICS Analysis")
    
    Set zelle2 = .Cells.Find("rating_status2", lookat:=xlWhole)
    posMonitoring2 = zelle2.Column
    Set zelle3 = .Cells.Find("customer_nbr", lookat:=xlWhole)
    posMonitoring3 = zelle3.Column
    
   intLastRow = .UsedRange.Rows.Count

    For i = 2 To intLastRow

            If .Cells(i, posMonitoring2).Value = "unclear" Then
                intCounter(1) = intCounter(1) + 1
                             
            End If
    Next i
 
   Unclear_Rating = intCounter
    End With
End Function`

I noticed a few things in your code - have a look at [UsedRange](https://stackoverflow.com/questions/11886284/usedrange-count-counting-wrong) as that may return the wrong number. Also when you use `FIND` it's good practice to check if it found anything - `IF NOT zelle2 IS NOTHING THEN` will return TRUE if the FIND found anything. You've also not accepted any answers on your previous questions. — Darren Bartrup-Cook, Jul 04 '18 at 15:59
How about some examples of data, actual results and desired results? And when you post the data, have it in a form that can be used by others (eg: text and NOT a screenshot, which cannot be pasted into an Excel sheet). There are many ways to count unique instances of data, both in VBA and with worksheet formulas. Or even with certain varieties of Pivot Tables. — Ron Rosenfeld, Jul 04 '18 at 17:47
Derek’s answer at https://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel would allow you do this without vba. But if you want to use vba, you can get at the sumproduct function through the application. — Jeremy Kahan, Jul 04 '18 at 17:58

score 0 · Answer 1 · answered Jul 05 '18 at 08:16

0

You can sort your data on rating_status2 and use subtotals (use the count function), like this you don't even need VBA.

answered Jul 05 '18 at 08:16

Dominique

16,450
15
56
112

i need to do it automatically in VBA) – Elena Jul 05 '18 at 09:09

vba how do not count rows (or count only once) if they have the same parameter

1 Answers1