Help with Counting Unique Duplicates

mandinko · Sep 17, 2014

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

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 · Sep 18, 2014

mandinko said:
Thanks That is much cleaner. Do you have any reference docs I can go and read on how to use the "--" characters and "<>1". I am just trying to understand how it calculates. The evaluate function is hard to read on my screen. I only have a 15 inch monitor.

SUMPRODUCT works with numerical arrays. The comparison (1/COUNTIF($B2:$E3,$B2:$E3))<>1) produces an array of logical values.

-- known as the double unary operator coerces (converts) these logical values, True or False, to respectively 1 or 0, therefore generating a numerical array that SUMPRODUCT can deal with.

M.

Marcelo Branco · Sep 18, 2014

For an excellent explanation about SUMPRODUCT and the use of --, take a look at

http://xldynamic.com/source/xld.SUMPRODUCT.html

M.

mandinko · Sep 18, 2014

Marco's script doesn't handle duplicate digits in the same row.. I just noticed this just now. Look at 9-6 and 9-5. That should be "1" and not "2".

Date	Draw1	Draw2	Draw3	Draw4	Repeat Digits
9/16/2014	1	1	7	9	3
9/16/2014	6	9	7	9	2
9/15/2014	6	1	9	3	2
9/14/2014	7	4	1	9	2
9/13/2014	9	7	3	8	3
9/12/2014	8	2	7	3	1
9/11/2014	6	0	5	7	2
9/10/2014	0	6	3	0	2
9/9/2014	8	6	5	6	2
9/8/2014	2	9	9	1	2
9/7/2014	6	3	0	1	0
9/6/2014	8	9	7	4	2
9/5/2014	1	1	1	4	2
9/4/2014	7	5	4	6	1
9/3/2014	1	9	0	4	2
9/2/2014	5	4	8	9	1
9/1/2014	3	6	8	0	2
8/31/2014	4	3	7	0	2
8/30/2014	7	3	2	8	2
8/29/2014	9	9	0	8	2
8/28/2014	7	1	8	9	1
8/27/2014	2	3	2	4	2
8/26/2014	9	4	4	5	2
8/25/2014	5	1	7	3	2
8/24/2014	2	4	7	4	2
8/23/2014	1	1	9	6	1
8/22/2014	8	2	0	7	2
8/21/2014	9	5	5	0	1
8/20/2014	7	3	5	1	2
8/19/2014	2	0	0	1	3

Marcelo Branco · Sep 18, 2014

mandinko said:
Marco's script doesn't handle duplicate digits in the same row.. I just noticed this just now. Look at 9-6 and 9-5. That should be "1" and not "2".

Sorry, I'm not following you.

Could you show us for which pairs of rows the formula is generating a wrong count?

M.

mandinko · Sep 18, 2014

Ztodd. I think yours does the same thing as well.. How do you deal with one or more duplicate numbers on the same row?

Aladin Akyurek · Sep 18, 2014

mandinko said:
Marco's script doesn't handle duplicate digits in the same row.. I just noticed this just now. Look at 9-6 and 9-5. That should be "1" and not "2".

Date
Draw1
Draw2
Draw3
Draw4
Repeat Digits

9/16/2014
1
1
7
9
3

9/16/2014
6
9
7
9
2

9/15/2014
6
1
9
3
2

9/14/2014
7
4
1
9
2

9/13/2014
9
7
3
8
3

9/12/2014
8
2
7
3
1

9/11/2014
6
5
7
2

9/10/2014
6
3
2

9/9/2014
8
6
5
6
2

9/8/2014
2
9
9
1
2

9/7/2014
6
3
1

9/6/2014
8
9
7
4
2

9/5/2014
1
1
1
4
2

9/4/2014
7
5
4
6
1

9/3/2014
1
9
4
2

9/2/2014
5
4
8
9
1

9/1/2014
3
6
8
2

8/31/2014
4
3
7
2

8/30/2014
7
3
2
8
2

8/29/2014
9
9
8
2

8/28/2014
7
1
8
9
1

8/27/2014
2
3
2
4
2

8/26/2014
9
4
4
5
2

8/25/2014
5
1
7
3
2

8/24/2014
2
4
7
4
2

8/23/2014
1
1
9
6
1

8/22/2014
8
2
7
2

8/21/2014
9
5
5
1

8/20/2014
7
3
5
1
2

8/19/2014
2
1
3

<TBODY>
</TBODY>

What are the desired results here - the figures in the Repeat Digits column? If so, is the figure of 3 correct?

Marcelo Branco · Sep 18, 2014

Please, tell us what is the expected result in this case? 2 or 3?

In other words, the 1s, that are in the same row, should be counted or not?

	A	B	C	D	E
1	Date	Draw1	Draw2	Draw3	Draw4
2	09/17/2014	1	1	7	9
3	09/16/2014	6	9	7	9

Marcelo Branco · Sep 18, 2014

See if this is what you want

	A	B	C	D	E	F
1	Date	Draw1	Draw2	Draw3	Draw4	Repeat Digits
2	09/17/2014	1	1	7	9	2
3	09/16/2014	6	9	7	9	2
4	09/15/2014	6	1	9	3	2
5	09/14/2014	7	4	1	9	2
6	09/13/2014	9	7	3	8	3
7	09/12/2014	8	2	7	3	1
8	09/11/2014	6	0	5	7	2
9	09/10/2014	0	6	3	0	1
10	09/09/2014	8	6	5	6	0
11	09/08/2014	2	9	9	1	1
12	09/07/2014	6	3	0	1	0
13	09/06/2014	8	9	7	4	1
14	09/05/2014	1	1	1	4	1
15	09/04/2014	7	5	4	6	1
16	09/03/2014	1	9	0	4	2
17	09/02/2014	5	4	8	9	1
18	09/01/2014	3	6	8	0	2
19	08/31/2014	4	3	7	0	2
20	08/30/2014	7	3	2	8	1
21	08/29/2014	9	9	0	8	2
22	08/28/2014	7	1	8	9	0
23	08/27/2014	2	3	2	4	1
24	08/26/2014	9	4	4	5	1
25	08/25/2014	5	1	7	3	1
26	08/24/2014	2	4	7	4	0
27	08/23/2014	1	1	9	6	0
28	08/22/2014	8	2	0	7	1
29	08/21/2014	9	5	5	0	1
30	08/20/2014	7	3	5	1	1
31	08/19/2014	2	0	0	1	0

Array formula in F2 copied down
=SUM(IF(FREQUENCY(IFERROR(MATCH($B2:$E2,$B3:$E3,0),""),COLUMN($B2:$E2)-COLUMN($B2)+1),1))

confirmed with Ctrl+Shift+Enter

M.

mandinko · Sep 18, 2014

Marcelo Branco said:
See if this is what you want

A
B
C
D
E
F
1
Date
Draw1
Draw2
Draw3
Draw4
Repeat Digits
2
09/17/2014
1
1
7
9
2
3
09/16/2014
6
9
7
9
2
4
09/15/2014
6
1
9
3
2
5
09/14/2014
7
4
1
9
2
6
09/13/2014
9
7
3
8
3
7
09/12/2014
8
2
7
3
1
8
09/11/2014
6
0
5
7
2
9
09/10/2014
0
6
3
0
1
10
09/09/2014
8
6
5
6
0
11
09/08/2014
2
9
9
1
1
12
09/07/2014
6
3
0
1
0
13
09/06/2014
8
9
7
4
1
14
09/05/2014
1
1
1
4
1
15
09/04/2014
7
5
4
6
1
16
09/03/2014
1
9
0
4
2
17
09/02/2014
5
4
8
9
1
18
09/01/2014
3
6
8
0
2
19
08/31/2014
4
3
7
0
2
20
08/30/2014
7
3
2
8
1
21
08/29/2014
9
9
0
8
2
22
08/28/2014
7
1
8
9
0
23
08/27/2014
2
3
2
4
1
24
08/26/2014
9
4
4
5
1
25
08/25/2014
5
1
7
3
1
26
08/24/2014
2
4
7
4
0
27
08/23/2014
1
1
9
6
0
28
08/22/2014
8
2
0
7
1
29
08/21/2014
9
5
5
0
1
30
08/20/2014
7
3
5
1
1
31
08/19/2014
2
0
0
1
0

<tbody>
</tbody>

Array formula in F2 copied down
=SUM(IF(FREQUENCY(IFERROR(MATCH($B2:$E2,$B3:$E3,0),""),COLUMN($B2:$E2)-COLUMN($B2)+1),1))

confirmed with Ctrl+Shift+Enter

M.

Let me explain. Put it this way. How many digits from 9-5 repeated in the 9-6 draw. So if the drawing was 1114 on 9/5 then on 9/6 drawing 8974. The 4 repeated therefore the repeated digit total would be 1. I think this is it. I have to plug it in and back test it to be sure but it does look promising.

ztodd · Sep 18, 2014

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

Help with Counting Unique Duplicates

mandinko

New Member

Marcelo Branco

MrExcel MVP

Excel Facts

Marcelo Branco

MrExcel MVP

mandinko

New Member

Marcelo Branco

MrExcel MVP

mandinko

New Member

Aladin Akyurek

MrExcel MVP

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

mandinko

New Member

ztodd

Board Regular

Similar threads

Forum statistics

Share this page

Help with Counting Unique Duplicates

New Member

MrExcel MVP

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock