Count unique values in data range

[bbo22]

Hi all,

I am building a big financial report, have a list of customer (per item) transactions. So if a customer bought a chair and a table their ID will show twice.
Now I need to find the number "active customers" for every month but using "Table

#VALUE!

 

[JackDanIce]

Have you tried to copy the column of customer IDs to a new column, then used Excel's Data->Remove Duplicates feature?

 

[JLGWhiz]

You can use Advanced Filter in VBA. Assume the data is in columns A:C.

Sub t()
Range("C1:C29").AdvancedFilter xlFilterCopy, , Range("E1"), True
MsgBox Range("E1").CurrentRegion.Rows.Count - 1
End Sub

 

[bbo22]

Have you tried to copy the column of customer IDs to a new column, then used Excel's Data->Remove Duplicates feature?

I need the data fixed because of automated queries. so must use formulas

 

[bbo22]

You can use Advanced Filter in VBA. Assume the data is in columns A:C.

Sub t()
Range("C1:C29").AdvancedFilter xlFilterCopy, , Range("E1"), True
MsgBox Range("E1").CurrentRegion.Rows.Count - 1
End Sub

Am I wrong but this doesn't count unique between dates? meaning Jan=X, Feb=Y, etc

 

[JLGWhiz]

Am I wrong but this doesn't count unique between dates? meaning Jan=X, Feb=Y, etc

It counts unique values in the range specified, no matter what they are. In your case, the specified range is all customer IDs. Note that the code assumed the data in the OP was in columns A:C. You might have other data in your original file in those columns and the data you posted could be in other columns beside A:C. The code is an example of how to use Advanced Filter to count unique items and not intended to represent your actual data. I have no idea how to do that with a formula, but I am not a formula expert.

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Month​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

jan/17​

[/TD]
[TD]

9​

[/TD]
[/TR]
</tbody>[/TABLE]


Data in range A1:C29; headers in row 1

E2 is a date (number) = 01/01/2017

Array formula in F2
=SUM(IF(FREQUENCY(IF(INT(B$2:B$29)>=E2,IF(INT(B$2:B$29)<=EOMONTH(E2,0),MATCH(C$2:C$29,C$2:C$29,0))),ROW(C$2:C$29)-ROW(C$2)+1),1))
Ctrl+Shift+Enter

Hope this helps

M.

 

[bbo22]

Great help! Thanks

 

[MickG]

Code option:-
Results columns "E & F".

[COLOR="Navy"]Sub[/COLOR] MG19Aug54
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Mth         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
         Mth = Format(Dn.Value, "mmm-yyyy")
            
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Mth) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Mth) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Not Dic(Mth).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Mth).Add (Dn.Offset(, 1).Value), ""
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
   
Range("E1:F1").Value = Array("Mth/Year", "Number")
   c = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = c + 1
        Cells(c, "E") = k
        Cells(c, "F") = Dic(k).Count
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick