Return most common number

[JustBEBE]

Hi, I need some help with a formula as I am stuck and can’t think anymore for a solution…let me explain what I need:

I have a col A1:A200 with 200 numbers from 1-20 that keeps repeating. In cell B1 I will add a number (5 for example).

I need a formula to give me the 3 most common numbers that are in the row underneath the number 5 out of the 200 numbers in col A1:A200.


A B
2 5
1
5
4
9
5
19
7
5
19
16
5
3

In the example above as you can see, number 19 is twice under number 5, so I need excel to give me that number (but top 3 numbers that are most common out of 200 numbers if possible)

Can someone help please? Thanks in advance!

 

[kevin9999]

Book1
	A	B	C
1	8	Top 3	Frequency
2	4	13	15
3	6	4	14
4	12	5	12
5	15
6	10
7	16
8	2
9	10
10	6
11	9
12	4
13	11
14	10
15	4
16	8
17	10
18	8
19	9
20	8
21	6
22	7
23	14
24	10
25	20
26	5
27	6
28	2
29	19
30	14
31	8
32	10
33	2
34	13
35	12
36	16
37	13
38	15
39	11
40	18
41	11
42	7
43	13
44	18
45	19
46	14
47	18
48	20
49	7
50	20
51	19
52	17
53	19
54	16
55	5
56	9
57	19
58	12
59	14
60	4
61	13
62	17
63	18
64	17
65	17
66	11
67	5
68	7
69	4
70	4
71	4
72	6
73	3
74	14
75	20
76	5
77	5
78	9
79	4
80	20
81	4
82	13
83	1
84	18
85	9
86	4
87	15
88	4
89	13
90	8
91	1
92	18
93	5
94	3
95	2
96	14
97	20
98	2
99	16
100	6
101	17
102	13
103	9
104	3
105	9
106	19
107	2
108	12
109	11
110	5
111	3
112	4
113	17
114	1
115	6
116	13
117	17
118	6
119	3
120	16
121	7
122	15
123	16
124	13
125	2
126	11
127	7
128	15
129	1
130	1
131	1
132	2
133	13
134	5
135	20
136	1
137	5
138	20
139	11
140	17
141	1
142	16
143	17
144	13
145	2
146	20
147	14
148	14
149	19
150	20
151	18
152	16
153	8
154	3
155	19
156	13
157	4
158	17
159	10
160	8
161	18
162	8
163	15
164	13
165	18
166	18
167	5
168	16
169	1
170	3
171	3
172	4
173	13
174	1
175	20
176	9
177	7
178	11
179	18
180	14
181	15
182	17
183	3
184	15
185	1
186	6
187	5
188	1
189	16
190	11
191	18
192	9
193	7
194	5
195	14
196	8
197	17
198	13
199	16
200	10
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=MODE(IF(ISERROR(MATCH($A$1:$A$200,B$1:B1,0)),$A$1:$A$200))
C2:C4	C2	=COUNTIF($A$1:$A$200,B2)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$A$200	B2:C4

 

[Legacy 143009]

Book1
	A	B	C
1	8	Top 3	Frequency
2	4	13	15
3	6	4	14
4	12	5	12
5	15
6	10
7	16
8	2
9	10
10	6
11	9
12	4
13	11
14	10
15	4
16	8
17	10
18	8
19	9
20	8
21	6
22	7
23	14
24	10
25	20
26	5
27	6
28	2
29	19
30	14
31	8
32	10
33	2
34	13
35	12
36	16
37	13
38	15
39	11
40	18
41	11
42	7
43	13
44	18
45	19
46	14
47	18
48	20
49	7
50	20
51	19
52	17
53	19
54	16
55	5
56	9
57	19
58	12
59	14
60	4
61	13
62	17
63	18
64	17
65	17
66	11
67	5
68	7
69	4
70	4
71	4
72	6
73	3
74	14
75	20
76	5
77	5
78	9
79	4
80	20
81	4
82	13
83	1
84	18
85	9
86	4
87	15
88	4
89	13
90	8
91	1
92	18
93	5
94	3
95	2
96	14
97	20
98	2
99	16
100	6
101	17
102	13
103	9
104	3
105	9
106	19
107	2
108	12
109	11
110	5
111	3
112	4
113	17
114	1
115	6
116	13
117	17
118	6
119	3
120	16
121	7
122	15
123	16
124	13
125	2
126	11
127	7
128	15
129	1
130	1
131	1
132	2
133	13
134	5
135	20
136	1
137	5
138	20
139	11
140	17
141	1
142	16
143	17
144	13
145	2
146	20
147	14
148	14
149	19
150	20
151	18
152	16
153	8
154	3
155	19
156	13
157	4
158	17
159	10
160	8
161	18
162	8
163	15
164	13
165	18
166	18
167	5
168	16
169	1
170	3
171	3
172	4
173	13
174	1
175	20
176	9
177	7
178	11
179	18
180	14
181	15
182	17
183	3
184	15
185	1
186	6
187	5
188	1
189	16
190	11
191	18
192	9
193	7
194	5
195	14
196	8
197	17
198	13
199	16
200	10
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=MODE(IF(ISERROR(MATCH($A$1:$A$200,B$1:B1,0)),$A$1:$A$200))
C2:C4	C2	=COUNTIF($A$1:$A$200,B2)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$A$200	B2:C4

Hi @kevin9999
I think what OP is asking for is, finding the top e most accuring numbers after a certain value. For ex. 19 occured twice after 5. Maybe other numbers occurred more than 19 did. Which 3 are the most frequent? But the condition is, they must appear right after 5. (Or any other pre-defined value)

 

[kevin9999]

Hi @kevin9999
I think what OP is asking for is, finding the top e most accuring numbers after a certain value. For ex. 19 occured twice after 5. Maybe other numbers occurred more than 19 did. Which 3 are the most frequent? But the condition is, they must appear right after 5. (Or any other pre-defined value)

Yes, you're right, I read it too quickly. I've switched off for tonight but be my guest

 

[Peter_SSs]

I need a formula to give me the 3 most common numbers that are in the row underneath the number 5

If you have all the latest functions, see if this does what you want. (Colour was just for my own benefit for checking)

23 01 05.xlsm
	A	B	C
1	8	Top 3	Frequency
2	4	9	4
3	5	16	3
4	12	20	2
5	15
6	5
7	16
8	2
9	10
10	6
11	5
12	4
13	11
14	10
15	5
16	9
17	10
18	8
19	9
20	5
21	9
22	7
23	14
24	10
25	20
26	5
27	6
28	2
29	19
30	14
31	8
32	10
33	2
34	13
35	12
36	16
37	13
38	15
39	11
40	18
41	11
42	7
43	13
44	18
45	19
46	14
47	18
48	20
49	7
50	20
51	19
52	17
53	19
54	16
55	5
56	9
57	19
58	12
59	14
60	4
61	13
62	17
63	18
64	17
65	17
66	11
67	5
68	7
69	4
70	4
71	4
72	6
73	3
74	14
75	20
76	7
77	5
78	9
79	4
80	20
81	4
82	13
83	1
84	18
85	9
86	4
87	15
88	4
89	13
90	8
91	1
92	18
93	5
94	16
95	2
96	14
97	20
98	2
99	16
100	6
101	17
102	13
103	9
104	3
105	9
106	19
107	2
108	12
109	11
110	5
111	3
112	4
113	17
114	1
115	6
116	13
117	17
118	6
119	3
120	16
121	7
122	15
123	16
124	13
125	2
126	11
127	7
128	15
129	1
130	1
131	1
132	2
133	13
134	5
135	20
136	1
137	5
138	20
139	11
140	17
141	1
142	16
143	17
144	13
145	2
146	20
147	14
148	14
149	19
150	20
151	18
152	16
153	8
154	3
155	19
156	13
157	4
158	17
159	10
160	8
161	18
162	8
163	15
164	13
165	18
166	18
167	5
168	16
169	1
170	3
171	3
172	4
173	13
174	1
175	20
176	9
177	7
178	11
179	18
180	14
181	15
182	17
183	3
184	15
185	1
186	6
187	5
188	1
189	16
190	11
191	18
192	9
193	7
194	5
195	14
196	8
197	17
198	13
199	16
200	10
Sheet2 (3)

Cell Formulas
Range	Formula
B2:C4	B2	=LET(u,UNIQUE(FILTER(A2:A200,A1:A199=5)),TAKE(SORT(HSTACK(u,COUNTIFS(A2:A200,u,A1:A199,5)),2,-1),3))
Dynamic array formulas.

 

[Legacy 143009]

Paste this formula to D2:

=MODE(IF(ISERROR(MATCH(IF($A$1:$A$200=$B$1,$A$2:$A$201),D$1:D1;0)),IF($A$1:$A$200=$B$1,$A$2:$A$201)))

Unfortunately, this formula counts the values only occurring more than once.

 

[JustBEBE]

Paste this formula to D2:

=MODE(IF(ISERROR(MATCH(IF($A$1:$A$200=$B$1,$A$2:$A$201),D$1:D1;0)),IF($A$1:$A$200=$B$1,$A$2:$A$201)))

Unfortunately, this formula counts the values only occurring more than once.

First of all I want to thank all of you for helping in this matter! You guys are great skilled!! @Flashbond this was working great! Exactly what I was looking for! Thanks a million.

I also tried @Peter_SSs yours but is giving me some error and I didn’t identified it yet, I will check and if not sorting I will ask you to help :D. Thanks @kevin9999 for trying, maybe I wasn’t the best at explaining after whole night trying to make it work with no success…buy anyway, thanks again you all for getting into this and for help!! Is working and is great!

 

[JustBEBE]

If you have all the latest functions, see if this does what you want. (Colour was just for my own benefit for checking)

23 01 05.xlsm
	A	B	C
1	8	Top 3	Frequency
2	4	9	4
3	5	16	3
4	12	20	2
5	15
6	5
7	16
8	2
9	10
10	6
11	5
12	4
13	11
14	10
15	5
16	9
17	10
18	8
19	9
20	5
21	9
22	7
23	14
24	10
25	20
26	5
27	6
28	2
29	19
30	14
31	8
32	10
33	2
34	13
35	12
36	16
37	13
38	15
39	11
40	18
41	11
42	7
43	13
44	18
45	19
46	14
47	18
48	20
49	7
50	20
51	19
52	17
53	19
54	16
55	5
56	9
57	19
58	12
59	14
60	4
61	13
62	17
63	18
64	17
65	17
66	11
67	5
68	7
69	4
70	4
71	4
72	6
73	3
74	14
75	20
76	7
77	5
78	9
79	4
80	20
81	4
82	13
83	1
84	18
85	9
86	4
87	15
88	4
89	13
90	8
91	1
92	18
93	5
94	16
95	2
96	14
97	20
98	2
99	16
100	6
101	17
102	13
103	9
104	3
105	9
106	19
107	2
108	12
109	11
110	5
111	3
112	4
113	17
114	1
115	6
116	13
117	17
118	6
119	3
120	16
121	7
122	15
123	16
124	13
125	2
126	11
127	7
128	15
129	1
130	1
131	1
132	2
133	13
134	5
135	20
136	1
137	5
138	20
139	11
140	17
141	1
142	16
143	17
144	13
145	2
146	20
147	14
148	14
149	19
150	20
151	18
152	16
153	8
154	3
155	19
156	13
157	4
158	17
159	10
160	8
161	18
162	8
163	15
164	13
165	18
166	18
167	5
168	16
169	1
170	3
171	3
172	4
173	13
174	1
175	20
176	9
177	7
178	11
179	18
180	14
181	15
182	17
183	3
184	15
185	1
186	6
187	5
188	1
189	16
190	11
191	18
192	9
193	7
194	5
195	14
196	8
197	17
198	13
199	16
200	10
Sheet2 (3)

Cell Formulas
Range	Formula
B2:C4	B2	=LET(u,UNIQUE(FILTER(A2:A200,A1:A199=5)),TAKE(SORT(HSTACK(u,COUNTIFS(A2:A200,u,A1:A199,5)),2,-1),3))
Dynamic array formulas.

I’ve just seen now, you said if I have all the latest functions, so I think that’s the reason is not working for me, I don’t think I have them…but thanks for your time! I appreciate

 

[Legacy 143009]

First of all I want to thank all of you for helping in this matter! You guys are great skilled!! @Flashbond this was working great! Exactly what I was looking for! Thanks a million.

I also tried @Peter_SSs yours but is giving me some error and I didn’t identified it yet, I will check and if not sorting I will ask you to help :D. Thanks @kevin9999 for trying, maybe I wasn’t the best at explaining after whole night trying to make it work with no success…buy anyway, thanks again you all for getting into this and for help!! Is working and is great!

Ahh sorry, I forgot a semicolon there because of my regional settings but I guess you've figured it out already Happy for it did help