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

 

[Marcelo Branco]

Save the file as macro enabled, i.e., with .xlsm extension

M.

 

[Marcelo Branco]

Ok I think that fixed it. Wow thank you. I might need to send you a paypal. Send me your email address.

Thanks,

Calvin

Forum Rules

Rule #8:
Soliciting business for yourself is not permitted on this board. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Members caught violating this rule will be banished from the forum.

M.

 

[ztodd]

Marcelo, isn't it obvious that mandinko is not soliciting business for himself? He's offering compensation to you. And you weren't soliciting business or offering solutions in exchange for compensation, so there were no rules broken.

 

[Marcelo Branco]

Marcelo, isn't it obvious that mandinko is not soliciting business for himself? He's offering compensation to you.

Yes, I understood it perfectly

And you weren't soliciting business or offering solutions in exchange for compensation, so there were no rules broken.

You are right, I'm not soliciting anything. But what if I send him my email?

I posted the Rule #8 just to make things clear for everybody.

M.

 

[ztodd]

The way I see it, you already offered him your solution- so even if you send him your email now, you're still not offering anything more than what he's already gotten, so still no rules broken. That's how I see it anyway.

 

[RoryA]

Just to clarify: we don't allow exchanging money in return for help, in any way, shape or form, no matter who initiates it.

 

[Marcelo Branco]

RoryA,

Thanks for the clarification.

M.

 

[ztodd]

He wouldn't be using the site to send money- all he's doing is asking for an email address. Any kind of money transfer would be a private manner, done off site. Not sure how that would or should have any bearing on this site. I don't mean to be disruptive or contentious- just confused on that point.

 

[RoryA]

The same would be true of any solicitation or offer of payment. The core principle of this site is that it's free, therefore we don't allow any reference to compensation, however well-meaning.

If you want to pay someone for work you can use the Mrexcel consulting services.

 

[mandinko]

Oh I am sorry. I am not soliciting business. I just wanted to show him my appreciation for helping me out. I apologize. I had been struggling with this for about a month now and I just wanted to give him a little token for helping me out. But now I know then I will not offer any funds or compensation in the future. sorry about that Calvin