Help with Counting Unique Duplicates

[mandinko]

Ok I will keep it simple with two rows. What I am trying to do is count unique duplicate values from one row into another row.

Date	Draw1	Draw2	Draw3	Draw4
9/16/2014	6	9	7	9
9/15/2014	6	1	9	3

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

I am trying to count the number of repeat digits that occurred in draw 9/15 to 9/16. I am stuck with this formula and it gives me 3 which should be 2. This is my current formula and I am stuck right now. Please note I want to count the unique digits. For example the 6 and 9 was drawn on September 15 but it also came up on 9/16 too but it is counting the 9 twice but it should be once.

=SUM(IFERROR(SUM(1/COUNTIF(B21:E21,B20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,C20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,D20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,E20)),0)
)

Any help would be greatly appreciated.

Thanks

Calvin

 

[mandinko]

That would be 3 repeats. The 144 repeated from the previous drawing

 

[ztodd]

ok- so one more example- If the drawing was 1145 on 9/5 then on 9/6 drawing 1944, what should the output be?

 

[mandinko]

That would be two. the 14 repeated from 9/5 to 9/6.

 

[Marcelo Branco]

If the drawing was 1144 on 9/5 then on 9/6 drawing 1944, would the repeated digit total be 2?

That would be 3 repeats. The 144 repeated from the previous drawing

Instead of a formula i suggest a UDF (User Defined Function)

Function CountRep(r1 As Range, r2 As Range) As Long
    Dim strAux As String, rCell As Range
    
    strAux = r2(1) & r2(2) & r2(3) & r2(4)
    For Each rCell In r1
        If InStr(1, strAux, rCell) Then
            CountRep = CountRep + 1
            strAux = Application.Substitute(strAux, rCell, "", 1)
        End If
    Next rCell
    
End Function

Usage
Formula in F2
=CountRep(B2:E2,B3:E3)
copy down

M.

 

[mandinko]

Okay I will try this when I get off from work. I will let you know. Thanks,

 

[mandinko]

Hmmn.

Why am i getting this error
[h=2]Ambiguous name detected error?[/h]

 

[Marcelo Branco]

Hmmn.

Why am i getting this error
Ambiguous name detected error?

Probably you have two Functions or two Subs with the same name - check it.

M.

 

[mandinko]

Marcelo.

I am stuck on entering this code because you have a reference in the code CountRep and then you say enter in "F2" the formula which contains CountRep. Maybe I am doing something wrong because Excel will not allow me to save it as a Macro right now.. Thanks Calvin

 

[Marcelo Branco]

Marcelo.

I am stuck on entering this code because you have a reference in the code CountRep and then you say enter in "F2" the formula which contains CountRep. Maybe I am doing something wrong because Excel will not allow me to save it as a Macro right now.. Thanks Calvin

Try
Select the code in post #24 and copy (Ctrl+C)
Alt+F11 to open the VBEditor
Menu
Insert > Module
Paste (Ctrl+V) in the right panel

Back to Excel
Assuming your data setup as post #18
Select the cell F2 and insert the formula
=CountRep(B2:E2,B3:E3)
Copy down

M.

 

[mandinko]

Ok I think that fixed it. Wow thank you. I might need to send you a paypal. Send me your email address. Also one final thing. Should I save it with a macro or without a macro. I don't want to lose that code

Thanks,

Calvin