Excel data as below:

I want to count only unique date rows with 1 place condition.

When column of place has sea only in a day, i hope exclude this case.

But when column of place has sea and river, I hope to count this case.

That is output : 2

CodePudding user response：

Use 2 dictionaries to count unique values for each condition and then summate them.

Sub Counts()
    Dim wb As Workbook, ws As Worksheet
    Dim LastRow As Long, i As Long, cell As Range

    Dim dict1, dict2, key, place As String
    Set dict1 = CreateObject("Scripting.Dictionary")
    Set dict2 = CreateObject("Scripting.Dictionary")
 
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    ' count sea or river
    With ws
        LastRow = .Cells(Rows.Count, "H").End(xlUp).Row
        For Each cell In .Range("H2:H" & LastRow)
            key = Format(cell, "YYYY-MM-DD")
            place = Trim(cell.Offset(0, -4)) ' col D
            If place = "sea" Then
               dict1(key) = dict1(key)   1
            ElseIf place = "river" Then
               dict2(key) = dict2(key)   1
            End If
        Next
    End With
   
    ' result
    For Each key In dict1.keys
        If dict2.exists(key) Then
            MsgBox key & " = " & dict1(key)   dict2(key)
        End If
    Next
End Sub

CodePudding user response：

I made code that is only made output unique days...i hope add 1 place condtion.

Sub Get_Count_Unique_Values()
Dim rng As Range

With CreateObject("Scripting.Dictionary")
For Each rng In Range("H7:H" & Cells(Rows.Count, "H").End(xlUp).Row)
If rng <> Empty Then
  If Not .exists(rng.Value) Then 
  .Add rng.Value, Nothing       
  End If
End If
Next
Range("S6").Value = .Count
End With
End Sub