Count Unique Values

[jschrock]

So I have this Data:

TxnDate	CustomerRefFullName	ItemServiceRefFullName
1/21/2015	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
1/21/2015	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
1/21/2015	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
1/7/2015	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential
1/7/2015	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential
1/7/2015	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I would like to do a count of only one instance per day. So, if I were to count this I would want Rucker, Ross to show as 2 instances. This represents a team going to this person so it isn't 6 visits, it is only one visit by 3 people. I can't group teams together because the people are interchangable.

Thanks!

 

[MickG]

Try this:-
Result start columns "F & G"

[COLOR="Navy"]Sub[/COLOR] MG26May42
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, k [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
   [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
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
                Dic(Dn.Value)(Dn.Offset(, -1).Value) = Empty
    [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = c + 1
        Cells(c, "F") = k: Cells(c, "G") = Dic(k).Count
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[caabyyc]

Another solution by using formula
Assume your source data is in A1:C9 and one person per day

1. E1 =IF(ROW(A1)>SUM(1/COUNTIF($A$1:$A$9,$A$1:$A$9)),"",INDEX(A:A,SMALL(IF(MATCH($A$1:$A$9,$A$1:$A$9,)=ROW($A$1:$A$9),ROW($A$1:$A$9),"/"),ROW(A1))))
2. F1 = IFERROR(VLOOKUP(E1,A:B,2,FALSE),"")
3. copy down until you see blank

after enter in E1, hit "ENTER+shift+control"

So I have this Data:

TxnDate	CustomerRefFullName	ItemServiceRefFullName
1/21/2015	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
1/21/2015	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
1/21/2015	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
1/7/2015	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential
1/7/2015	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential
1/7/2015	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I would like to do a count of only one instance per day. So, if I were to count this I would want Rucker, Ross to show as 2 instances. This represents a team going to this person so it isn't 6 visits, it is only one visit by 3 people. I can't group teams together because the people are interchangable.

Thanks!

 

[Aladin Akyurek]

What would be the result if we had an additional record of 1/7/2015 with John South?

 

[jschrock]

It would show up as a unique value for John South.

I want to be able to count by customer name how many times they were visited in a given month.

 

[Aladin Akyurek]

It would show up as a unique value for John South.

I want to be able to count by customer name how many times they were visited in a given month.

Row\Col	A​	B​	C​
1​	TxnDate	CustomerRefFullName	ItemServiceRefFullName
2​	1/21/2015​	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
3​	1/21/2015​	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
4​	1/21/2015​	Rucker, Ross	2000 CA:2200 CA-M:2202 Mulch
5​	1/7/2015​	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential
6​	1/7/2015​	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential
7​	1/7/2015​	Rucker, Ross	2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential
8​	1/7/2015​	South, John	2001 CA:2200 CA-M:2201 Mowing:2201.1 Residential

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",IF(B2:B8<>"",
    MATCH(A2:A8&"|"&B2:B8,A2:A8&"|"&B2:B8,0))),
    ROW(A2:A8)-ROW(A2)+1),1))