Find the 1st/2nd/3rd Common Text w/ Criteria

[_eNVy_]

Hi all,

Looking for a formula that will give me the result of the most common text within my dataset.

I currently have, for the first common text result the following formula :

{=INDEX(tblMain[Placement Provider],MODE(IF(tblMain[Reporting Fin-Year]=RIGHT(E4,5),IF(tblMain[Placement Provider]<>"",MATCH(tblMain[Placement Provider],tblMain[Placement Provider],0)))))}

tblMain[Placement Provider] holds all the text where I wish to find the recurring texts.
tblMain[Reporting Fin-Year] is the financial year of the and the basis of my first criteria range
RIGHT(E4,5) extracts the financial year in the format that is presented in tblMain[Reporting Fin-Year]

However, I am finding it extremely difficult to get the 2nd and 3rd (potentially 4th and 5th) most common texts. Especially with the blanks in tblMainPlacement Provider] and the criteria of the financial year.

Any and all help will be appreciated.

 

[CA_Punit]

I have done as per my example. You can modify it as per your requirement

Book1
	A	B	C	D	E	F	G	H	I
3				Year	Text			Most Five Frequently Occuring Text	Number of times Occuring
4	Year	2020		01-02-2020	a			a	4
5				01-02-2020	b			s	3
6								v	2
7				03-02-2020	fd			c	2
8				04-02-2020	s			f	2
9				05-02-2020	a			g	2
10				06-02-2020	d			h	3
11				07-02-2020	A
12				08-02-2020	x
13				09-02-2020	v
14				10-02-2020	v
15
16				01-03-2020	c
17				02-03-2020	s
18
19				04-03-2020	c
20				05-03-2020	f
21				06-03-2020	r
22				07-03-2020	e
23				08-03-2020	s
24
25				10-03-2020	g
26				11-03-2020	h
27				12-03-2020	h
28				13-03-2020	a
29
30				15-03-2020	f
31				16-03-2020	g
32				17-03-2020	h
33				18-03-2020	hj
34				19-03-2021	a
35
36				21-03-2021	f
37				22-03-2021	r
38				23-03-2021	e
39				24-03-2021	r
40				25-03-2021	w
41				26-03-2021	w
42				27-03-2021	w
Sheet2

Cell Formulas
Range	Formula
H4:H10	H4	=INDEX($E$4:$E$42,AGGREGATE(15,6,IF(MATCH(FREQUENCY(IFERROR(MATCH(IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),0),FALSE),ROW(D4:D42)-ROW(D4)+1),LARGE(FREQUENCY(IFERROR(MATCH(IF($D$4:$D$42/(YEAR($D$4:$D$42)=$B$4),$E$4:$E$42),IF($D$4:$D$42/(YEAR($D$4:$D$42)=$B$4),$E$4:$E$42),0),FALSE),ROW($D$4:$D$42)-ROW($D$4)+1),{1;2;3;4;5}),0),IFERROR(MATCH(IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),0),FALSE)),{1;2;3;4;5;6;7}))
I4:I10	I4	=SUM(--(IF(YEAR($D$4:$D$42)=$B$4,$E$4:$E$42)=H4))
Dynamic array formulas.

 

[_eNVy_]

I have done as per my example. You can modify it as per your requirement

Book1
	A	B	C	D	E	F	G	H	I
3				Year	Text			Most Five Frequently Occuring Text	Number of times Occuring
4	Year	2020		01-02-2020	a			a	4
5				01-02-2020	b			s	3
6								v	2
7				03-02-2020	fd			c	2
8				04-02-2020	s			f	2
9				05-02-2020	a			g	2
10				06-02-2020	d			h	3
11				07-02-2020	A
12				08-02-2020	x
13				09-02-2020	v
14				10-02-2020	v
15
16				01-03-2020	c
17				02-03-2020	s
18
19				04-03-2020	c
20				05-03-2020	f
21				06-03-2020	r
22				07-03-2020	e
23				08-03-2020	s
24
25				10-03-2020	g
26				11-03-2020	h
27				12-03-2020	h
28				13-03-2020	a
29
30				15-03-2020	f
31				16-03-2020	g
32				17-03-2020	h
33				18-03-2020	hj
34				19-03-2021	a
35
36				21-03-2021	f
37				22-03-2021	r
38				23-03-2021	e
39				24-03-2021	r
40				25-03-2021	w
41				26-03-2021	w
42				27-03-2021	w
Sheet2

Cell Formulas
Range	Formula
H4:H10	H4	=INDEX($E$4:$E$42,AGGREGATE(15,6,IF(MATCH(FREQUENCY(IFERROR(MATCH(IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),0),FALSE),ROW(D4:D42)-ROW(D4)+1),LARGE(FREQUENCY(IFERROR(MATCH(IF($D$4:$D$42/(YEAR($D$4:$D$42)=$B$4),$E$4:$E$42),IF($D$4:$D$42/(YEAR($D$4:$D$42)=$B$4),$E$4:$E$42),0),FALSE),ROW($D$4:$D$42)-ROW($D$4)+1),{1;2;3;4;5}),0),IFERROR(MATCH(IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),IF(D4:D42/(YEAR(D4:D42)=B4),E4:E42),0),FALSE)),{1;2;3;4;5;6;7}))
I4:I10	I4	=SUM(--(IF(YEAR($D$4:$D$42)=$B$4,$E$4:$E$42)=H4))
Dynamic array formulas.

Thanks a lot! I sampled your formula and it worked on a small range. I need to just alter it to look at the Years as Financial Years (2019/20, 2020/21).

When I tried to expand the range however, I get the 'ran out of resources' error message!!!! Urgh!
Will have to contact my tech team to do something about this.