Get the 2nd, 3rd more frecuent values in a filtered table with more than 1 numeric column

[drom]

Hi and Thanks in advance!

I have a table with:

• Max 9 columns ( for this eg: lets say 6 columns A:F)
• Unknown rows (for this eg: 23 rows)
  • So My Table's databodyrange is A9:F30
  • My Table's Header is A8:F8
• B9:F30 are cells with numbers, not blank cells, not text cells, numeric values, for this eg, I am using the formula, =RANDBETWEEN(1,100)

I know how to get the 1st, 2nd, 3rd, 4rd and 5th more frecuent values in this table using VBA
When this table is filtered by Column A, by country, and/or when is not filtered (Easy with VBA)

To get the more frecuent number I can use:

• =Mode(B9:F30)
• =MODE(Table3[[VALUES1]:[VALUES5]])

But I would like a formula:

1. To get the more frecuent number in my table when the table is filtered by column A
2. To get the 2nd more frecuent number whenThe table is not filtered
3. To get the 2nd more frecuent number when The table is filtered

I guess If I get the answer for my 3 questions, I will be able to get the 3rd, 4rd, and 5th more frecuent numbers in this table when filtered or not filtered

I am attaching my WorkSheet:, this Worksheet is done just for this eg:

Book1
	A	B	C	D	E	F	G
1
2		This table can be filtered by Country
3
4			1st More Frecuent Number:	??
5			2nd More Frecuent Number:	??
6			3rd More Frecuent Number:	??
7
8	Country	VALUES1	VALUES2	VALUES3	VALUES4	VALUES5
9	Spain	77	61	75	24	59
10	France	96	18	60	13	97
11	UK	89	4	37	51	53
12	USA	98	42	21	9	94
13	Spain	18	61	70	47	72
14	USA	76	25	44	65	91
15	Portugal	69	38	44	21	22
16	Italy	97	43	7	85	3
17	Belgium	24	21	11	31	42
18	Mexico	79	44	26	33	48
19	Mexico	25	47	58	81	64
20	uk	27	71	45	4	12
21	Italy	74	54	35	17	73
22	Usa	76	60	60	42	77
23	USA	53	75	6	70	55
24	UK	96	51	88	11	52
25	Italy	58	64	32	39	64
26	Spain	59	25	21	62	48
27	Spain	79	40	34	13	47
28	France	47	84	85	37	17
29	Portugal	6	28	90	66	48
30	France	49	94	70	89	82
31
Sheet2

Cell Formulas
Range	Formula
B9:F30	B9	=RANDBETWEEN(1,100)

 

[BSALV]

Map1
	A	B	C	D	E	F	G
4			1st More Frecuent Number:	72	72
5			2nd More Frecuent Number:	21	21
6			3rd More Frecuent Number:	63	29
7
8	Country	VALUES1	VALUES2	VALUES3	VALUES4	VALUES5	visible
9	Spain	92	9	34	80	64	1
10	France	26	19	41	40	86	1
12	USA	75	72	12	43	14	1
13	Spain	48	33	24	15	55	1
14	USA	15	18	76	31	46	1
15	Portugal	36	45	51	88	12	1
16	Italy	41	26	87	79	23	1
21	Italy	45	5	66	70	36	1
22	Usa	68	63	23	72	73	1
23	USA	85	46	63	57	87	1
25	Italy	13	21	72	100	65	1
26	Spain	46	78	100	27	45	1
27	Spain	58	39	46	32	92	1
28	France	12	33	33	69	89	1
29	Portugal	86	44	60	15	86	1
30	France	26	52	84	40	22	1
31
32
33
34
35
36
37
38
39
40		72	5		72	5
41		21	5		21	5
42		63	3		29	2
43		67	3		55	2
44		15	2		63	2
45		29	2		96	2
Blad1

Cell Formulas
Range	Formula
D4:D6	D4	=+B40
E4:E6	E4	=+E40
B9:F10,B12:F16,B21:F23,B25:F30	B9	=RANDBETWEEN(1,100)
G9:G10,G12:G16,G21:G23,G25:G30	G9	=(SUBTOTAL(102,B9:F9)>0)+0

Sub counting()
     Dim bVis, Res()
     With Sheets("blad1")
          a = Range("A8").CurrentRegion.Resize(, 7)
          ReDim Res(1 To UBound(a) * UBound(a, 2), 1 To 5)
          For i = 2 To UBound(a)
               bVis = a(i, 7) <> 0
               For j = 2 To UBound(a, 2) - 1
                    r = Application.Match(a(i, j), Application.Index(Res, 0, 1), 0)
                    If Not IsNumeric(r) Then ptr = ptr + 1: r = ptr: Res(ptr, 1) = a(i, j): Res(ptr, 4) = a(i, j)
                    Res(r, 2) = Res(r, 2) + 1
                    If bVis Then Res(r, 5) = Res(r, 5) + 1
               Next
          Next

          With .Range("B40")
               .Resize(1000, 5).ClearContents
               With .Resize(ptr, 5)
                    .Value = Res
                    .Resize(, 2).Sort .Range("B1"), xlDescending, Header:=xlNo
                    .Offset(, 3).Resize(, 2).Sort .Range("E1"), xlDescending, Header:=xlNo
               End With
          End With
     End With
End Sub

 

[BSALV]

in the yellow range with formulas, the integer part = number of times, the decimal part * 10,000 = the number himself.

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M
7
8	Country	VALUES1	VALUES2	VALUES3	VALUES4	VALUES5	visible
9	Spain	99	15	46	2	89	1		99	15	46	2	89
10	France	4	72	15	32	67	1		4	72	15	32	67
12	USA	10	8	30	71	53	1		10	8	30	71	53
13	Spain	43	78	48	52	39	1		43	78	48	52	39
14	USA	74	99	27	5	78	1		74	99	27	5	78
15	Portugal	78	43	81	4	90	1		78	43	81	4	90
16	Italy	55	31	98	73	56	1		55	31	98	73	56
21	Italy	14	88	44	89	42	1		14	88	44	89	42
22	Usa	25	52	21	20	19	1		25	52	21	20	19
23	USA	68	4	24	24	24	1		68	4	24	24	24
25	Italy	7	52	88	81	59	1		7	52	88	81	59
26	Spain	49	100	27	27	57	1		49	100	27	27	57
27	Spain	43	46	82	3	68	1		43	46	82	3	68
28	France	73	20	30	23	74	1		73	20	30	23	74
29	Portugal	81	29	85	4	61	1		81	29	85	4	61
30	France	21	69	64	97	95	1		21	69	64	97	95
31
32
33
34
35
36
37
38
39		VBA - All			VBA - filtered				all	filtered
40		4	4		4	4		1	4,0078	4,0004
41		78	4		43	3		2	4,0056	3,0081
42		56	4		78	3		3	4,0004	3,0078
43		89	3		52	3		4	3,0089	3,0052
44		8	3		27	3		5	3,0081	3,0043
45		20	3		81	3		6	3,0052	3,0027
46		43	3		24	3		7	3,0043	3,0024
47		52	3		99	2		8	3,0027	2,0099
48		27	3		15	2		9	3,0024	2,0089
49		81	3		46	2		10	3,0020	2,0088
50		24	3		89	2
Blad1

Cell Formulas
Range	Formula
B9:F10,B12:F16,B21:F23,B25:F30	B9	=RANDBETWEEN(1,100)
G9:G10,G12:G16,G21:G23,G25:G30	G9	=(SUBTOTAL(102,B9:F9)>0)+0
I9:M10,I25:M30,I21:M23,I12:M16	I9	=+$G9*B9
H40:H49	H40	=SEQUENCE(10)
I40:I49	I40	=LARGE(COUNTIF($B$9:$F$30,SEQUENCE(100))+SEQUENCE(100)/10000,H40)
J40:J49	J40	=LARGE(COUNTIF($I$9:$M$30,SEQUENCE(100))+SEQUENCE(100)/10000,H40)
Dynamic array formulas.

 

[Peter_SSs]

See if this is what you want.

With your Excel versions, formulas to be confirmed with Ctrl+Shift+Enter, not just Enter

drom.xlsm
	A	B	C	D	E	F
4			1st More Frecuent Number:	88
5			2nd More Frecuent Number:	1
6			3rd More Frecuent Number:	33
7
8	Country	VALUES1	VALUES2	VALUES3	VALUES4	VALUES5
9	Spain	33	58	88	89	1
10	France	87	76	35	36	97
11	UK	83	49	16	55	88
12	USA	73	2	100	38	81
13	Spain	76	25	64	42	1
14	USA	80	85	40	12	80
15	Portugal	9	5	34	32	48
16	Italy	6	100	10	50	75
17	Belgium	50	41	88	43	26
18	Mexico	85	32	76	8	86
19	Mexico	23	82	97	57	23
20	uk	47	54	67	63	52
21	Italy	67	79	13	24	1
22	Usa	5	13	68	7	81
23	USA	39	53	86	86	99
24	UK	97	87	51	52	33
25	Italy	96	44	10	33	57
26	Spain	73	36	48	21	74
27	Spain	88	10	35	13	64
28	France	18	84	8	12	70
29	Portugal	64	68	58	49	93
30	France	1	75	43	75	43
31
32
Sheet1

Cell Formulas
Range	Formula
D4	D4	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,Table3[[VALUES1]:[VALUES5]]))
D5	D5	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,IF(Table3[[VALUES1]:[VALUES5]]<>D4,Table3[[VALUES1]:[VALUES5]])))
D6	D6	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,IF(Table3[[VALUES1]:[VALUES5]]<>D4,IF(Table3[[VALUES1]:[VALUES5]]<>D5,Table3[[VALUES1]:[VALUES5]]))))
Press CTRL+SHIFT+ENTER to enter array formulas.

In my table above
88 occurs 4 times
1 occurs 4 times
33 occurs 3 times


After filtering for France & Italy

drom.xlsm
	A	B	C	D	E	F
4			1st More Frecuent Number:	75
5			2nd More Frecuent Number:	10
6			3rd More Frecuent Number:	1
7
8	Country	VALUES1	VALUES2	VALUES3	VALUES4	VALUES5
10	France	87	76	35	36	97
16	Italy	6	100	10	50	75
21	Italy	67	79	13	24	1
25	Italy	96	44	10	33	57
28	France	18	84	8	12	70
30	France	1	75	43	75	43
31
32
Sheet1

Cell Formulas
Range	Formula
D4	D4	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,Table3[[VALUES1]:[VALUES5]]))
D5	D5	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,IF(Table3[[VALUES1]:[VALUES5]]<>D4,Table3[[VALUES1]:[VALUES5]])))
D6	D6	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,IF(Table3[[VALUES1]:[VALUES5]]<>D4,IF(Table3[[VALUES1]:[VALUES5]]<>D5,Table3[[VALUES1]:[VALUES5]]))))
Press CTRL+SHIFT+ENTER to enter array formulas.

75 occurs 3 times
10 occurs 2 times
1 occurs 2 times

If there are no numbers that occur multiple times, the formula returns #N/A. Example

drom.xlsm
	A	B	C	D	E	F
4			1st More Frecuent Number:	75
5			2nd More Frecuent Number:	43
6			3rd More Frecuent Number:	#N/A
7
8	Country	VALUES1	VALUES2	VALUES3	VALUES4	VALUES5
10	France	87	76	35	36	97
28	France	18	84	8	12	70
30	France	1	75	43	75	43
31
32
Sheet1

Cell Formulas
Range	Formula
D4	D4	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,Table3[[VALUES1]:[VALUES5]]))
D5	D5	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,IF(Table3[[VALUES1]:[VALUES5]]<>D4,Table3[[VALUES1]:[VALUES5]])))
D6	D6	=MODE(IF(SUBTOTAL(102,OFFSET(Table3[VALUES1],ROW(Table3[VALUES1])-ROW(Table3[#Headers])-1,,1,5))>0,IF(Table3[[VALUES1]:[VALUES5]]<>D4,IF(Table3[[VALUES1]:[VALUES5]]<>D5,Table3[[VALUES1]:[VALUES5]]))))
Press CTRL+SHIFT+ENTER to enter array formulas.