Return most frequent text strings with a filter/if statement

[effdeearr]

Hello,

So, I have a list of data. For these purposes my data is all text strings.

Is there a way I can use a formula to return the top 3 most frequent strings in a column, while also using an IF statement (or similar) to include only a subset of the overall dataset?

I'll use an example:

Category	Filter
Item 1	B
Item 1	B
Item 1	B
Item 2	A
Item 2	B
Item 4	A
Item 4	A
Item 4	B
Item 4	B

I know that I can use formulae to return the most frequent strings from the category column. I've used a formula found online (as it happens I know the formula works but can't claim to fully understand how it works):

Using
=OFFSET($A$1,MODE(MATCH($A$2:$A$40,$A$2:$A$40,0)),0)
In an initial cell, then
=OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D1,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0)
and
=OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D2,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0)
etc
[I would be interested to know if anyone can actually explain to me what this formula is doing?]

I can't find a way to use a formula / nest a function into this formula that would then only return the most frequent entries that also are only in Filter B, or Filter A, for example.

I've tried to upload a mini-sheet but not sure if it'll work.

Sincerely

EffDeeArr

Book4.xlsx
	A	B	C	D	E
1	String	Filter		Telephone2	11
2	Telephone1	1		Telephone4	11
3	Telephone1	2		Telephone3	5
4	Telephone1	2		Telephone1	3
5	Telephone10	1		Telephone10	1
6	Telephone11	1		Telephone11	1
7	Telephone12	1		Telephone12	1
8	Telephone13	1
9	Telephone2	1
10	Telephone2	1
11	Telephone2	1
12	Telephone2	1
13	Telephone2	1
14	Telephone2	1
15	Telephone2	1
16	Telephone2	1
17	Telephone2	1
18	Telephone2	1
19	Telephone2	1
20	Telephone3	1
21	Telephone3	1
22	Telephone3	1
23	Telephone3	1
24	Telephone3	1
25	Telephone4	1
26	Telephone4	1
27	Telephone4	1
28	Telephone4	1
29	Telephone4	1
30	Telephone4	1
31	Telephone4	1
32	Telephone4	1
33	Telephone4	1
34	Telephone4	1
35	Telephone4	1
36	Telephone5	1
37	Telephone6	1
38	Telephone7	1
39	Telephone8	1
40	Telephone9	1
41
42
Sheet1

Cell Formulas
Range	Formula
D1	D1	=OFFSET($A$1,MODE(MATCH($A$2:$A$40,$A$2:$A$40,0)),0)
E1:E7	E1	=COUNTIFS(A:A,D1)
D2:D7	D2	=OFFSET($A$1,MODE(IF(COUNTIF($D$1:$D1,$A$2:$A$40)=0,MATCH($A$2:$A$40,$A$2:$A$40,0)+{0,0})),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

Is this what you're trying to do?

Book1 (version 1)19.2.21.xlsx
	A	B	C	D	E
1	String	Filter		Telephone4	4
2	Telephone1	C		Telephone2	2
3	Telephone1	C		Telephone7	1
4	Telephone1	B		Telephone8	1
5	Telephone10	C
6	Telephone11	D
7	Telephone12	B
8	Telephone13	D
9	Telephone2	C
10	Telephone2	B
11	Telephone2	B
12	Telephone2	A
13	Telephone2	C
14	Telephone2	D
15	Telephone2	C
16	Telephone2	B
17	Telephone2	D
18	Telephone2	D
19	Telephone2	A
20	Telephone3	B
21	Telephone3	B
22	Telephone3	B
23	Telephone3	D
24	Telephone3	C
25	Telephone4	D
26	Telephone4	A
27	Telephone4	A
28	Telephone4	C
29	Telephone4	C
30	Telephone4	C
31	Telephone4	A
32	Telephone4	B
33	Telephone4	D
34	Telephone4	B
35	Telephone4	A
36	Telephone5	C
37	Telephone6	D
38	Telephone7	A
39	Telephone8	A
40	Telephone9	D
Sheet14

Cell Formulas
Range	Formula
D1:E4	D1	=SORT(CHOOSE({1,2},UNIQUE(FILTER(A2:A40,B2:B40="A")),COUNTIFS(A2:A40,UNIQUE(FILTER(A2:A40,B2:B40="A")),B2:B40,"A")),2,-1)
Dynamic array formulas.

 

[Fluff]

If you have the LET function with 365, another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	String	Filter
2	Telephone1	C		Filter	A		Telephone4	4
3	Telephone1	E		Rows	3		Telephone2	2
4	Telephone1	B					Telephone7	1
5	Telephone10	C
6	Telephone11	D
7	Telephone12	B
8	Telephone13	D
9	Telephone2	C
10	Telephone2	B
11	Telephone2	B
12	Telephone2	A
13	Telephone2	C
14	Telephone2	D
15	Telephone2	C
16	Telephone2	B
17	Telephone2	D
18	Telephone2	D
19	Telephone2	A
20	Telephone3	B
21	Telephone3	B
22	Telephone3	B
23	Telephone3	D
24	Telephone3	C
25	Telephone4	D
26	Telephone4	A
27	Telephone4	A
28	Telephone4	C
29	Telephone4	C
30	Telephone4	C
31	Telephone4	A
32	Telephone4	B
33	Telephone4	D
34	Telephone4	B
35	Telephone4	A
36	Telephone5	C
37	Telephone6	D
38	Telephone7	A
39	Telephone8	A
40	Telephone9	D
Master

Cell Formulas
Range	Formula
G2:H4	G2	=LET(Uni,UNIQUE(FILTER(A2:A40,B2:B40=E2)),INDEX(SORT(CHOOSE({1,2},Uni,COUNTIFS(A:A,Uni,B:B,E2)),2,-1),SEQUENCE(MIN(COUNTA(Uni),E3)),{1,2}))
Dynamic array formulas.

 

[effdeearr]

Is this what you're trying to do?

Book1 (version 1)19.2.21.xlsx
	A	B	C	D	E
1	String	Filter		Telephone4	4
2	Telephone1	C		Telephone2	2
3	Telephone1	C		Telephone7	1
4	Telephone1	B		Telephone8	1
5	Telephone10	C
6	Telephone11	D
7	Telephone12	B
8	Telephone13	D
9	Telephone2	C
10	Telephone2	B
11	Telephone2	B
12	Telephone2	A
13	Telephone2	C
14	Telephone2	D
15	Telephone2	C
16	Telephone2	B
17	Telephone2	D
18	Telephone2	D
19	Telephone2	A
20	Telephone3	B
21	Telephone3	B
22	Telephone3	B
23	Telephone3	D
24	Telephone3	C
25	Telephone4	D
26	Telephone4	A
27	Telephone4	A
28	Telephone4	C
29	Telephone4	C
30	Telephone4	C
31	Telephone4	A
32	Telephone4	B
33	Telephone4	D
34	Telephone4	B
35	Telephone4	A
36	Telephone5	C
37	Telephone6	D
38	Telephone7	A
39	Telephone8	A
40	Telephone9	D
Sheet14

Cell Formulas
Range	Formula
D1:E4	D1	=SORT(CHOOSE({1,2},UNIQUE(FILTER(A2:A40,B2:B40="A")),COUNTIFS(A2:A40,UNIQUE(FILTER(A2:A40,B2:B40="A")),B2:B40,"A")),2,-1)
Dynamic array formulas.

So sorry for the delayed response - especially given how quickly you got back to me. Massive thanks.

I haven't tested it yet (the computer I was doing my checks on is my personal one, with non-365 office on it, but I'll try this again when I'm next on my work machine. The formula definitely looks like it'll do what I'm after though. Will report back.

Again - massive thanks for your response.

 

[effdeearr]

If you have the LET function with 365, another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	String	Filter
2	Telephone1	C		Filter	A		Telephone4	4
3	Telephone1	E		Rows	3		Telephone2	2
4	Telephone1	B					Telephone7	1
5	Telephone10	C
6	Telephone11	D
7	Telephone12	B
8	Telephone13	D
9	Telephone2	C
10	Telephone2	B
11	Telephone2	B
12	Telephone2	A
13	Telephone2	C
14	Telephone2	D
15	Telephone2	C
16	Telephone2	B
17	Telephone2	D
18	Telephone2	D
19	Telephone2	A
20	Telephone3	B
21	Telephone3	B
22	Telephone3	B
23	Telephone3	D
24	Telephone3	C
25	Telephone4	D
26	Telephone4	A
27	Telephone4	A
28	Telephone4	C
29	Telephone4	C
30	Telephone4	C
31	Telephone4	A
32	Telephone4	B
33	Telephone4	D
34	Telephone4	B
35	Telephone4	A
36	Telephone5	C
37	Telephone6	D
38	Telephone7	A
39	Telephone8	A
40	Telephone9	D
Master

Cell Formulas
Range	Formula
G2:H4	G2	=LET(Uni,UNIQUE(FILTER(A2:A40,B2:B40=E2)),INDEX(SORT(CHOOSE({1,2},Uni,COUNTIFS(A:A,Uni,B:B,E2)),2,-1),SEQUENCE(MIN(COUNTA(Uni),E3)),{1,2}))
Dynamic array formulas.

Much like I said to the other poster - sorry for the massive delay in response, I really appreciate your quick suggestion. I'll try this out too when I'm next on my work computer with 365.

Thanks again for your response. Truly grateful.

 

[Fluff]

Glad we could help & thanks for the feedback.