Frequent Text - Duplicates

[SaraWitch]

Hello all,

I'm using the Index, Mode and Match array formula to show the most frequently repeated text. However, I wonder if there's a formula to show if there is more than one match, i.e. three cooking and three exercise class):

Most Attended Activity	Cooking	Age Range Most Attended		18 - 25
Number of Females	2	Most Common Housing Status		PRS
Number of Males	3	Services Most Known To		RSI
Number of Transgender	1
Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To
Cooking		Transgender	18 - 25	PRS	RSI
Cooking		Male	18 - 25	PRS	RSI
Cooking		Female	26 - 35	Rough sleeping	Homeworks
Exercise class		Male	26 - 35	Rough sleeping	Homeworks
Exercise class		Male	46 and over	TA	RRP
Exercise class		Female	46 and over	HF	None

Any advise would be gratefully received!

Ta muchly!

 

[sandy666]

something like this?

Column1	Count
Cooking	3
Exercise class	3
Male	3
18 - 25	2
26 - 35	2
46 and over	2
Female	2
Homeworks	2
PRS	2
RSI	2
Rough sleeping	2
HF	1
None	1
RRP	1
TA	1
Transgender	1

 

[SaraWitch]

Similar, although I can use a filter for this because I do have quite a few DDLs. I could record all this on a separate page, but I just wondered if there was a way to show in the summary box that cooking and exercise class were most attended, e.g. Most Attended Activity: Cooking, Exercise Class...?

 

[sandy666]

sure, like this?

Column1	Count
Cooking	3
Exercise class	3
Male	3

anyway, you can filter table from post#2 as you wish to get what you want

 

[sandy666]

or

Max	TextMax
3	Cooking, Exercise class, Male
2	18 - 25, 26 - 35, 46 and over, Female, Homeworks, PRS, RSI, Rough sleeping
1	HF, None, RRP, TA, Transgender

 

[SaraWitch]

That's great and I have added a separate summary page; however, I wanted this summary box to look like this (I have over typed the formulas, so refer to my first posting for these (order rearranged, but formulas the same!)):

Most Attended Activity:	Cooking, Exercise class	Number of Females:		2
Most Common Housing Status:	PRS, Rough sleeping	Number of Males:		3
Services Most Known To:	RSI, Homeworks	Number of Transgender:		1
Age Range Most Attended:	18 - 25, 26 - 35
Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To
Cooking		Transgender	18 - 25	PRS	RSI
Cooking		Male	18 - 25	PRS	RSI
Cooking		Female	26 - 35	Rough sleeping	Homeworks
Exercise class		Male	26 - 35	Rough sleeping	Homeworks
Exercise class		Male	46 and over	TA	RRP
Exercise class		Female	46 and over	HF	None

 

[CA_Punit]

Here you can get the Name Appearing in Column H1 and H2,

Book1
	A	B	C	D	E	F	G	H
1	Most Attended Activity	Cooking	Age Range Most Attended		18 - 25			Cooking
2	Number of Females	2	Most Common Housing Status		PRS
3	Number of Males	3	Services Most Known To		RSI
4	Number of Transgender	1
5
6	Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To
7	Cooking		Transgender	18 - 25	PRS	RSI
8	Cooking		Male	18 - 25	PRS	RSI
9	Cooking		Female	26 - 35	Rough sleeping	Homeworks
10	Cooking		Male	26 - 35	Rough sleeping	Homeworks
11	Exercise class		Male	46 and over	TA	RRP
12	Exercise class		Female	46 and over	HF	None
13
14
Sheet1

Cell Formulas
Range	Formula
H1:H6	H1	=IFERROR(INDEX($A$7:$A$12,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($A$7:$A$12,$A$7:$A$12,0),(ROW($A$7:$A$12)-ROW($A$7)+1))^0*COUNTIF($A$7:$A$12,$A$7:$A$12)=MAX(COUNTIF($A$7:$A$12,$A$7:$A$12)))*(ROW($A$7:$A$12)-ROW($A$7)+1))),ROWS($G$1:G1))),"")

 

[CA_Punit]

Oh Sorry I didnt See the Post 6, Let me again give a try as per your requirement

 

[sandy666]

ok, I'll leave you to solve with a formula ?

 

[Bo_Ry]

For MS 365
B2
=TEXTJOIN(", ",,INDEX(A7:A12,MODE.MULT(MATCH(A7:A12,A7:A12,))))

For other versions
=INDEX(A7:A12,MIN(MODE.MULT(MATCH(A7:A12,A7:A12,))))&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),2)),"")&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),3)),"")

Book1
	A	B	C	D	E	F	G	H	I	J
1	Most Attended Activity	Cooking, Exercise class	Age Range Most Attended		18 - 25 , 26 - 35 , 46 and over		Cooking, Exercise class			18 - 25 , 26 - 35 , 46 and over
2	Number of Females	2	Most Common Housing Status		PRS
3	Number of Males	3	Services Most Known To		RSI
4	Number of Transgender	1
5
6	Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To
7	Cooking		Transgender	18 - 25	PRS	RSI
8	Cooking		Male	18 - 25	PRS	RSI
9	Cooking		Female	26 - 35	Rough sleeping	Homeworks
10	Exercise class		Male	26 - 35	Rough sleeping	Homeworks
11	Exercise class		Male	46 and over	TA	RRP
12	Exercise class		Female	46 and over	HF	None
Sheet1

Cell Formulas
Range	Formula
B1,E1	B1	=TEXTJOIN(", ",,INDEX(A7:A12,MODE.MULT(MATCH(A7:A12,A7:A12,))))
G1,J1	G1	=INDEX(A7:A12,MIN(MODE.MULT(MATCH(A7:A12,A7:A12,))))&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),2)),"")&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),3)),"")