10 most frequently repeated numbers

[juanam]

In a one column data range I have several numbers corresponding to bingo balls that have come out.
In that column I will continue to add future ball values.

I want to get the 10 most frequently repeated numbers.

I have used MODE. MULT function, but it only returns some values, I need the full 10 values, with the following numbers that have come out more frequently.

Sorry for my bad English, I speak Spanish and use google translator

bingo.xlsx
	A	B
1	BALLS	MODE
2	25	25
3	68	68
4	7	7
5	8	8
6	7	50
7	68	#N/D
8	8	#N/D
9	25	#N/D
10	68	#N/D
11	25	#N/D
12	7
13	8
14	50
15	50
16	33
17	79
18	10
19	65
20	58
21	37
22	70
23	78
24	21
25	36
26	38
27	32
28	90
29	16
30	46
31	64
32	69
33	52
34	71
35	26
36	29
37	84
38	19
39	40
40	72
41	17
42	40
43	33
44	19
45	72
46	79
47	10
48	65
49	58
50	37
51	70
52	78
53	21
54	36
55	38
56	16
57	46
58	64
59	69
60	52
61	71
62	26
63	29
64	84
65	50
66	90
67	32
68	74
69	14
70	6
71	88
72	31
73	51
74	45
75	34
76	13
77	53
78	5
79	80
80	24
81	85
82	20
83	11
84	18
85	49
86	1
87	87
88	15
89	73
90	42
91	86
92	35
93	76
94	63
95	47
96	66
97	56
98	62
99	4
100	89
101	57
102	41
103	82
104	12
105	9
106	22
107
BINGO

Cell Formulas
Range	Formula
B2:B11	B2	=MODE.MULT(balls_range)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JEC]

One method

=INDEX(SEQUENCE(99);MATCH(LARGE(COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);SEQUENCE(10));COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);0))

With let:

=LET(x;COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);INDEX(SEQUENCE(99);MATCH(LARGE(x;SEQUENCE(10));x;0)))

 

[juanam]

One method

=INDEX(SEQUENCE(99);MATCH(LARGE(COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);SEQUENCE(10));COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);0))

With let:

=LET(x;COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);INDEX(SEQUENCE(99);MATCH(LARGE(x;SEQUENCE(10));x;0)))

Sorry that it took me a while to respond because I had to translate the functions to my excel in Spanish.

The results are consistent, I see that it completes the other values that it could not get.

But I have seen that both formulas return me data that is repeated with the same frequency as others.

With what criteria do the formulas extract the numbers 90,84,79,78,72,71 that are repeated 2 times and not 50,17,33,10,37 that are also repeated 2 times?

That is, as long as there are several values that are repeated the same number of times (currently 2), will it choose only some at "random"?

 

[JEC]

Yes, that is random in this case

PS: here an Excel formula translator as add-in

Excel Functions Translator add-in | Easy-Excel.com

 

[juanam]

Yes, that is random in this case

PS: here an Excel formula translator as add-in

Excel Functions Translator add-in | Easy-Excel.com

Ok thanks a lot for your help.
But is there no other "safer" solution?
So I only see the numbers that MODE.MULT function does not extract.are the ones it choose randomly.
I know it is difficult to take a criterion for these missing numbers, another solution would be to take the numbers that have the same frequency and appear first but it would not be a safe formula anyway. ?

I think that until I add more data to the database I will not have a safe statistical solution. ?

Anyway, I will mark your answer as solved since you have taken the time to help me

 

[JEC]

At the background, MODE.MULT is random as well for the numbers it is showing. It depends on the positions of the specific numbers in the dataset.
Since bingo is random, it will always be random which ball comes first

 

[juanam]

That's what I was thinking...
If at one point I have +10 values that are repeated with the same frequency, MODE.MULT will choose 10 because that is what I requested ?

PS
Microsoft translator add-on could not translate the simpler formula "SEQUENCE" into Spanish, which in Spanish is "SECUENCIA".
All the others it translated well, even the most different "LARGE" than in Spanish is "K.ESIMO.MAYOR" ?

 

[juanam]

At the background, MODE.MULT is random as well for the numbers it is showing. It depends on the positions of the specific numbers in the dataset.

Since bingo is random, it will always be random which ball comes first

Excuse me @JEC , will it be possible to make a macro that copies 10 frequent numbers in another column and orders them from smallest to largest?

Or if it is possible that they are ordered in that same PLAY column without a macro, even better.

From what I see they are ordered from highest to lowest in the PLAY column and I want them ordered but in reverse.

 

[Peter_SSs]

In that column I will continue to add future ball values.

It looks like you have already made column A into a formal Excel Table. That is a good idea as the formulas below will automatically adjust as new values are added.
I think that you can do all you want without the frequency column but I am a little unsure whether you only want exactly 10 numbers returned or if there are more with equal (10th) frequency if you want them returned as well.

I think that one of these should be what you want though.

In your sample 5 numbers occur 3 times. Those numbers are 7, 8, 25, 50, and 68
Then you have another 25 numbers that occur twice.

The formula in C2 below will ensure that those 5 values that occurred 3 times will be in the final list but then just choose enough of the numbers that occurred twice, starting from the smallest of those numbers, to make up to a total of 10 numbers.
If there was more than 10 numbers all with equal highest frequency this C2 formula would just choose the smallest 10 of those numbers.

The formula in D2 lists all the numbers that occurred 3 times or 2 times, since the 10th highest frequency is 2. The whole list is ordered from smallest to largest.

21 11 27.xlsm
	A	B	C	D
1	BALLS		10 Only	10 plus equals
2	25		7	7
3	68		8	8
4	7		10	10
5	8		16	16
6	7		19	19
7	68		21	21
8	8		25	25
9	25		26	26
10	68		50	29
11	25		68	32
12	7			33
13	8			36
14	50			37
15	50			38
16	33			40
17	79			46
18	10			50
19	65			52
20	58			58
21	37			64
22	70			65
23	78			68
24	21			69
25	36			70
26	38			71
27	32			72
28	90			78
29	16			79
30	46			84
31	64			90
32	69
33	52
34	71
35	26
36	29
37	84
38	19
39	40
40	72
41	17
42	40
43	33
44	19
45	72
46	79
47	10
48	65
49	58
50	37
51	70
52	78
53	21
54	36
55	38
56	16
57	46
58	64
59	69
60	52
61	71
62	26
63	29
64	84
65	50
66	90
67	32
68	74
69	14
70	6
71	88
72	31
73	51
74	45
75	34
76	13
77	53
78	5
79	80
80	24
81	85
82	20
83	11
84	18
85	49
86	1
87	87
88	15
89	73
90	42
91	86
92	35
93	76
94	63
95	47
96	66
97	56
98	62
99	4
100	89
101	57
102	41
103	82
104	12
105	9
106	22
10 most

Cell Formulas
Range	Formula
C2:C11	C2	=LET(D,Table1[BALLS],SORT(INDEX(UNIQUE(SORTBY(D,COUNTIFS(D,D),-1,D,1)),SEQUENCE(10))))
D2:D31	D2	=LET(D,Table1[BALLS],SORT(UNIQUE(FILTER(D,COUNTIF(D,D)>=LARGE(COUNTIF(D,UNIQUE(D)),10)))))
Dynamic array formulas.

 

[juanam]

Hi @Peter_SSs I loved your ideas, I'm going to apply them along with mine.

Column B is not necessary I put it simply to see how often the numbers were repeated

Any solution for a macro that copies values calculated with my previous formula from column D (PLAY) to a right column E (ORDERED) and that orders them from smallest to largest automatically (see shared image).

I thought of a macro to avoid doing the work manually, unless you know a way to modify the matrix formula of column D so that the data is ordered from smallest to largest.

@JEC0's formula column D:

=INDEX(SEQUENCE(99);MATCH(LARGE(COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);SEQUENCE(10));COUNTIF($A$2:$A$106;SEQUENCE(99))+(SEQUENCE(99)/1000);0))