Count Unique Values

[jschrock]

So I have this Data:

[TABLE="width: 826"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]TxnDate[/TD]
[TD] CustomerRefFullName[/TD]
[TD]ItemServiceRefFullName[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2015[/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2202 Mulch[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2015[/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2202 Mulch[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2015 [/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2202 Mulch[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2015 [/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2015[/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2015 [/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 826"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]TxnDate[/TD]
[TD] CustomerRefFullName[/TD]
[TD]ItemServiceRefFullName[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2015[/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2202 Mulch[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2015[/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2202 Mulch[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2015 [/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2202 Mulch[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2015 [/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2015[/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2015 [/TD]
[TD] Rucker, Ross[/TD]
[TD]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/TD]
[/TR]
</tbody>[/TABLE]

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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]TxnDate[/td][td]CustomerRefFullName[/td][td]ItemServiceRefFullName[/td][/tr]

[tr][td]

2​

[/td][td]

1/21/2015​

[/td][td]Rucker, Ross[/td][td]2000 CA:2200 CA-M:2202 Mulch[/td][/tr]

[tr][td]

3​

[/td][td]

1/21/2015​

[/td][td]Rucker, Ross[/td][td]2000 CA:2200 CA-M:2202 Mulch[/td][/tr]

[tr][td]

4​

[/td][td]

1/21/2015​

[/td][td]Rucker, Ross[/td][td]2000 CA:2200 CA-M:2202 Mulch[/td][/tr]

[tr][td]

5​

[/td][td]

1/7/2015​

[/td][td]Rucker, Ross[/td][td]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/td][/tr]

[tr][td]

6​

[/td][td]

1/7/2015​

[/td][td]Rucker, Ross[/td][td]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/td][/tr]

[tr][td]

7​

[/td][td]

1/7/2015​

[/td][td]Rucker, Ross[/td][td]2000 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/td][/tr]

[tr][td]

8​

[/td][td]

1/7/2015​

[/td][td]South, John[/td][td]2001 CA:2200 CA-M:2201 Mowing:2201.1 Residential[/td][/tr]
[/table]

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))