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.

 

[Peter_SSs]

You do not need a macro, just wrap any formula that gives you the correct numbers in a SORT function
=SORT(your_original_formula)

 

[juanam]

Thank you very much @JEC and @Peter_SSs for your help.

I just need last favor, I want to horizontally transpose and concatenate PLAY1 and PLAY2 columns values and separate data with hyphens "-" into a single cell.

I have tried using these formulas but I have not been able to keep data in a single cell and also I have not been able to remove the last hyphen from the list.

G12:

=CONCATENATE(TRANSPOSE(C2#)&"-")

G13:

=CONCATENATE(TRANSPOSE(D2#)&"-")

I want to achieve this:

 

[juanam]

EDIT(I couldn't edit post):

Please moderators merge this post

I have already achieved what I wanted with a macro.

Option Explicit
Function transposeRange(Rg As Range)
Application.ScreenUpdating = False
    Dim xCell As Range
    Dim xStr As String
    For Each xCell In Rg
        If Not IsEmpty(xCell.Value) Then
            xStr = xStr & xCell.Value & " - "
        End If
    Next
    transposeRange = Left(xStr, Len(xStr) - 2)
            
End Function

G12:

=transposeRange(C2#)

G13:

=transposeRange(D2#)

 

[Peter_SSs]

I have already achieved what I wanted with a macro.

A few comments

• Not sure if it matters or if you are aware but your function leaves a space character at the right of the final text string (because you only subtracted 2 from the length of the string and not 3)
  
  
• If you really want to use a user-defined function, this would do the same job.
  
  

  Function transposeRange(Rg As Range) As String
    transposeRange = Join(Application.Transpose(Rg), " - ")
  End Function

  
  
• You don't need a user-defined function as this simple worksheet function will do the same thing.
  
  

  =TEXTJOIN(" - ",,C2#)

 

[juanam]

@Peter_SSs, you are a genious!
You've simplified both codes a lot and they really do what I want.
Thank you! ?

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[juanam]

@Peter_SSs , I have another question that I don't know how to solve.
I have modified spreadsheet a bit, adding other columns.

I need the ranges of columns G, H, and I to dynamically match the number of rows in column F.

By adding more balls to column A, column F changes its number of rows by more or less, and that data must be transferred to the 3 named columns.

That is, when increasing or decreasing the number of rows in column F, the row ranges of the referred columns must be changed automatically.

CODE:

bingo_mr_excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	BALLS		PLAY 1	PLAY 2	COMPLETE LIST	UNIQUE LIST	RANDOM	PLAY 3	PLAY 4
2	27		22	10	22	10	0,471706779	38	10
3	80		30	14	30	14	0,212740422	80	14
4	37		38	17	38	17	0,733714313	17	17
5	51		46	22	46	22	0,223321016	60	22
6	58		47	30	47	30	0,22125355	73	30
7	36		59	38	59	38	0,986319821	14	38
8	7		60	46	60	46	0,525320639	30	46
9	73		73	47	73	47	0,997634596	10	47
10	34		80	60	80	59	0,349424659	47	59
11	57		88	88	88	60	0,035124476	88	60
12	41				10	73	0,374463199	46	73		PLAY 1	22-30-38-46-47-59-60-73-80-88
13	89				14	80	0,563181753	22	80		PLAY 2	10-14-17-22-30-38-46-47-60-88
14	8				17	88	0,232853569	59	88
15	47				22
16	44				30						PLAY 3	10-14-17-22-30
17	78				38						PLAY 4	38-46-47-59-60
18	42				46
19	30				47
20	18				60
21	38				88
22	74
BINGO

Cell Formulas
Range	Formula
C2:C11	C2	=SORT(INDEX(SEQUENCE(90),MATCH(LARGE(COUNTIF($A$2:$A$492,SEQUENCE(90))+(SEQUENCE(90)/1000),SEQUENCE(10)),COUNTIF($A$2:$A$492,SEQUENCE(90))+(SEQUENCE(90)/1000),0)))
D2:D11	D2	=SORT(INDEX(UNIQUE(SORTBY(Tabl_Bingo3781011,COUNTIFS(Tabl_Bingo3781011,Tabl_Bingo3781011),-1,Tabl_Bingo3781011,1)),SEQUENCE(10)))
E2:E11	E2	=SORT(C2#)
F2:F14	F2	=SORT(UNIQUE(E2:E21))
G2:G14	G2	=RAND()
H2:H14	H2	=INDEX($F$2:$F$14,RANK(G2,$G$2:$G$14))
I2:I14	I2	=SORT(VALUE(H2:H14))
E12:E21	E12	=SORT(D2#)
L12	L12	=TEXTJOIN("-",,C2#)
L13	L13	=TEXTJOIN("-",,D2#)
L16	L16	=TEXTJOIN("-",,I2:I6)
L17	L17	=TEXTJOIN("-",,I7:I11)
Dynamic array formulas.

SCREENSHOTs:

 

[juanam]

Edit
P.S
If when changing the number of rows in the columns, previous formulas remain, they should also be eliminated so that there are no errors.
I don't know if it can be done without a macro, I also accept solutions with a macro.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	BALLS		PLAY 1	PLAY 2	COMPLETE LIST	UNIQUE LIST	RANDOM	PLAY 3	PLAY 4
2	27		44	7	44	7	0.1590849	74	7
3	80		47	8	47	8	0.4017499	57	8
4	37		51	18	51	18	0.8837606	30	18
5	51		57	27	57	27	0.5476801	47	27
6	58		58	30	58	30	0.9538756	8	30
7	36		73	34	73	34	0.6485373	37	34
8	7		74	36	74	36	0.9961027	7	36
9	73		78	37	78	37	0.7703597	34	37
10	34		80	38	80	38	0.9369385	27	38
11	57		89	41	89	41	0.6832667	36	41
12	41				7	44	0.3296241	58	44
13	89				8	47	0.9470497	18	47
14	8				18	51	0.1576737	78	51
15	47				27	57	0.600134	41	57
16	44				30	58	0.152523	80	58
17	78				34	73	0.4970814	51	73
18	42				36	74	0.6227906	38	74
19	30				37	78	0.1732519	73	78
20	18				38	80	0.0926537	89	80
21	38				41	89	0.587384	44	89
22	74
23
Main

Cell Formulas
Range	Formula
C2:C11	C2	=SORT(INDEX(SEQUENCE(90),MATCH(LARGE(COUNTIF($A$2:$A$488,SEQUENCE(90))+(SEQUENCE(90)/1000),SEQUENCE(10)),COUNTIF($A$2:$A$488,SEQUENCE(90))+(SEQUENCE(90)/1000),0)))
D2:D11	D2	=SORT(INDEX(UNIQUE(SORTBY(Tabl_Bingo3781011,COUNTIFS(Tabl_Bingo3781011,Tabl_Bingo3781011),-1,Tabl_Bingo3781011,1)),SEQUENCE(10)))
E2:E11	E2	=SORT(C2#)
F2:F21	F2	=SORT(UNIQUE(E2:E21))
G2:G21	G2	=RANDARRAY(F2#)
H2:H21	H2	=INDEX(F2#,COUNTIFS(G2#,">"&G2#)+1)
I2:I21	I2	=SORT(H2#)
E12:E21	E12	=SORT(D2#)
Dynamic array formulas.

 

[juanam]

@Fluff another genious in this Community ?‍

Thank you very kind ?