Search code examples
excelvbaexcel-formulacountunique

How to get the distinct values of a range with two criteria in Excel with formula or VBA?

I have a column with around 16k rows with duplicate dates in which should be searched for the Count of Unique dates between a week. Here SHEET WMR in the codeis the sheet where the unique counts should be stored in row DAYS #8 and above it are the dates that should be used as conditions. I have tried Sum/countifs array formula but i got Division with 0 Error. Then I tried this loop

Dim WMR, MHR, P, D As Worksheet

Set WMR = Sheets("WMREP")
Set MHR = Sheets("MACH-HRS")
Set P = Sheets("PRODUCTION")
Set D = Sheets("DELAYS")

Dim last_row, last_row1, last_row2 As Long
last_row = MHR.Cells(Rows.Count, 1).End(xlUp).Row
last_row1 = P.Cells(Rows.Count, 1).End(xlUp).Row
last_row2 = D.Cells(Rows.Count, 1).End(xlUp).Row
counter = 1

For x = 8 To 11
    For y = 3 To last_row1
        If P.Cells(y, 1) >= WMR.Cells(7, x - 1) And P.Cells(y, 1) < WMR.Cells(7, x) Then
            If P.Cells(y, 1) <> P.Cells(y - 1, 1) Then
                counter = counter + 1
            End If
        End If
    Next y
    WMR.Cells(8, x) = counter
    counter = 0
Next x

But it returns values more than 7 and it is not right. I am stuck with this one. Formula or VBA solution would be appreciated.

Sheet P in the code

Solution

  • Well, if you had access to DA-functions, it would be rather easy:

    =COUNT(UNIQUE(FILTER(Sheet2!$A2:$A16000,Sheet2!$A2:$A16000>G7-1,Sheet2!$A2:$A16000<G7+7)))

    But not having access to those, you could try:

    =SUM(--(FREQUENCY(IF((Sheet2!$A2:$A16000>G7-1)*(Sheet2!$A2:$A16000<G7+7),Sheet2!$A2:$A16000),Sheet2!$A2:$A16000)>0))

    Note: This second option needs to be confirmed through CtrlShiftEnter. Both options can be put in G7 on Sheet1 and be dragged to the right.

    Know, that if needed, you can also make the reference to column A dynamic so you won't have to hardcode rows.