Top 5 numbers in a range

[Thehub]

I have a table with 130 rows and 5 columns. Each cell contains an integer between 1 and 49 inclusive. No number is repeated within a row.

I need to find the top 5 different numbers within that range. Anyone help?

 

[Thehub]

What exactly are you looking for?
The top 5 per row, the top 5 per column?

Hi. Thanks for looking. It is the top 5 or top 5 most frequent numbers in the range. For example, 39 may occur 20 times, then 2 occurs 18 times etc. And I do have the LET function

 

[Thehub]

Could you leave an example of your spreadsheet here so we can help you better?

Thanks!

Could you leave an example of your spreadsheet here so we can help you better?

Thanks!

Sample_001.xlsx
	I
8
Sheet1

Hi. I think I've done the correctly

 

[Fluff]

How about

=LET(Rng,A1:E130,Seq,SEQUENCE(49),Qty,COUNTIF(Rng,Seq),AGGREGATE(15,6,Seq/(Qty>=LARGE(Qty,5)),SEQUENCE(5)))

 

[Thehub]

How about

=LET(Rng,A1:E130,Seq,SEQUENCE(49),Qty,COUNTIF(Rng,Seq),AGGREGATE(15,6,Seq/(Qty>=LARGE(Qty,5)),SEQUENCE(5)))

WOW! That worked. Many thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

Couple of other options if you wanted it sorted, with or without the count

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	45	27	17	40	9		Number	Count		Number only
2	28	28	13	43	14		23	7		23
3	37	30	27	47	32		11	6		11
4	5	37	23	11	22		28	5		28
5	12	22	9	12	28		9	4		9
6	28	22	48	44	23		16	4		16
7	10	18	23	36	35
8	23	25	15	2	1
9	3	16	4	23	31
10	40	19	5	30	10
11	45	13	23	11	36
12	24	31	22	18	18
13	10	49	25	1	46
14	9	16	16	21	49
15	19	15	11	37	21
16	33	17	28	20	29
17	11	39	9	23	36
18	21	27	11	39	5
19	24	21	26	7	16
20	19	1	33	39	11
List

Cell Formulas
Range	Formula
G2:H6	G2	=LET(Rng,A1:E20,Seq,SEQUENCE(49),Qty,COUNTIF(Rng,Seq),Ary,CHOOSE({1,2},Seq,Qty),INDEX(SORT(Ary,2,-1),SEQUENCE(5),{1,2}))
J2:J6	J2	=LET(Rng,A1:E20,Seq,SEQUENCE(49),Qty,COUNTIF(Rng,Seq),Ary,CHOOSE({1,2},Seq,Qty),INDEX(SORT(Ary,2,-1),SEQUENCE(5),1))
Dynamic array formulas.

 

[Thehub]

Couple of other options if you wanted it sorted, with or without the count

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	45	27	17	40	9		Number	Count		Number only
2	28	28	13	43	14		23	7		23
3	37	30	27	47	32		11	6		11
4	5	37	23	11	22		28	5		28
5	12	22	9	12	28		9	4		9
6	28	22	48	44	23		16	4		16
7	10	18	23	36	35
8	23	25	15	2	1
9	3	16	4	23	31
10	40	19	5	30	10
11	45	13	23	11	36
12	24	31	22	18	18
13	10	49	25	1	46
14	9	16	16	21	49
15	19	15	11	37	21
16	33	17	28	20	29
17	11	39	9	23	36
18	21	27	11	39	5
19	24	21	26	7	16
20	19	1	33	39	11
List

Cell Formulas
Range	Formula
G2:H6	G2	=LET(Rng,A1:E20,Seq,SEQUENCE(49),Qty,COUNTIF(Rng,Seq),Ary,CHOOSE({1,2},Seq,Qty),INDEX(SORT(Ary,2,-1),SEQUENCE(5),{1,2}))
J2:J6	J2	=LET(Rng,A1:E20,Seq,SEQUENCE(49),Qty,COUNTIF(Rng,Seq),Ary,CHOOSE({1,2},Seq,Qty),INDEX(SORT(Ary,2,-1),SEQUENCE(5),1))
Dynamic array formulas.

Thanks. That's an even better solution.

 

[Fluff]

My pleasure.