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!

 

[Fluff]

@SaraWitch
When posting to the board please use the "Generate Output" button, rather than the "Table Only Output", that way we can what formulae you have used, which makes it easier for members to help you.

 

[SaraWitch]

Sorry; here you go:

RSI Activities WEST.xlsm
	A	B	C	D	E	F	G	H
1		RSI Activities and Training WEST - Demographic
2		Summary
3
4		Most Attended Activity:		Number of Females:		2
5		Most Common Housing Status:		Number of Males:		3
6		Services Most Known To:		Number of Transgender:		1
7		Age Range Most Attended:
8
9		Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To	Date
10		Cooking		Transgender	18 - 25	Social	RSI
11		Cooking		Male	18 - 25	Social	RSI
12		Cooking		Female	26 - 35	Social	RSI
13		Exercise class		Male	26 - 35	PRS	None
14		Exercise class		Male	18 - 25	PRS	None
15		Exercise class		Female	26 - 35	PRS	None
16		Cinema
17		Cinema
18		Cinema
19
Demographics TEST (2)

Cell Formulas
Range	Formula
F4	F4	=IF(COUNTIF($D$10:$D$129,"Female")=0,"",COUNTIF($D$10:$D$129,"Female"))
F5	F5	=IF(COUNTIF($D$10:$D$129,"Male")=0,"",COUNTIF($D$10:$D$129,"Male"))
F6	F6	=IF(COUNTIF($D$10:$D$129,"Transgender")=0,"",COUNTIF($D$10:$D$129,"Transgender"))
C4	C4	=TEXTJOIN(";",TRUE,IFERROR(INDEX($B$10:$B$130,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($B$10:$B$130,$B$10:$B$130,0),(ROW($B$10:$B$130)-ROW($B$10)+1))^0*COUNTIF($B$10:$B$130,$B$10:$B$130)=MAX(COUNTIF($B$10:$B$130,$B$10:$B$130)))*(ROW($B$10:$B$130)-ROW($B$10)+1))),ROW($H$4:H130))),""))
C5	C5	=TEXTJOIN(";",TRUE,IFERROR(INDEX($F$10:$F$15,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($F$10:$F$15,$F$10:$F$15,0),(ROW($B$10:$B$15)-ROW($B$10)+1))^0*COUNTIF($F$10:$F$15,$F$10:$F$15)=MAX(COUNTIF($F$10:$F$15,$F$10:$F$15)))*(ROW($B$10:$B$15)-ROW($B$10)+1))),ROW($H$4:H13))),""))
C6	C6	=TEXTJOIN(";",TRUE,IFERROR(INDEX($G$10:$G$15,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($G$10:$G$15,$G$10:$G$15,0),(ROW($B$10:$B$15)-ROW($B$10)+1))^0*COUNTIF($G$10:$G$15,$G$10:$G$15)=MAX(COUNTIF($G$10:$G$15,$G$10:$G$15)))*(ROW($B$10:$B$15)-ROW($B$10)+1))),ROW($H$4:H13))),""))
C7	C7	=TEXTJOIN(";",TRUE,IFERROR(INDEX($E$10:$E$15,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($E$10:$E$15,$E$10:$E$15,0),(ROW($B$10:$B$15)-ROW($B$10)+1))^0*COUNTIF($E$10:$E$15,$E$10:$E$15)=MAX(COUNTIF($E$10:$E$15,$E$10:$E$15)))*(ROW($B$10:$B$15)-ROW($B$10)+1))),ROW($H$4:H13))),""))

I see that even changing the rows in the original table to include the rest of the rows, doesn't work either!

RSI Activities WEST.xlsm
	A	B	C	D	E	F	G	H
1		Most Attended Activity:		Number of Females:		2
2		Most Common Housing Status:	Social;PRS	Number of Males:		3
3		Services Most Known To:	RSI;None	Number of Transgender:		1
4		Age Range Most Attended:	18 - 25;26 - 35
5
6		Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To	Date
7		Cooking		Transgender	18 - 25	Social	RSI
8		Cooking		Male	18 - 25	Social	RSI
9		Cinema		Female	26 - 35	Social	RSI
10		Exercise class		Male	26 - 35	PRS	None
11		Exercise class		Male	18 - 25	PRS	None
12		Exercise class		Female	26 - 35	PRS	None
13
Demographics TEST

Cell Formulas
Range	Formula
F1	F1	=IF(COUNTIF($D$7:$D$126,"Female")=0,"",COUNTIF($D$7:$D$126,"Female"))
F2	F2	=IF(COUNTIF($D$7:$D$126,"Male")=0,"",COUNTIF($D$7:$D$126,"Male"))
F3	F3	=IF(COUNTIF($D$7:$D$126,"Transgender")=0,"",COUNTIF($D$7:$D$126,"Transgender"))
C1	C1	=TEXTJOIN("; ",TRUE,IFERROR(INDEX($B$7:$B$130,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($B$7:$B$130,$B$7:$B$130,0),(ROW($B$7:$B$130)-ROW($B$7)+1))^0*COUNTIF($B$7:$B$130,$B$7:$B$130)=MAX(COUNTIF($B$7:$B$130,$B$7:$B$130)))*(ROW($B$7:$B$130)-ROW($B$7)+1))),ROW($H$1:H10))),""))
C2	C2	=TEXTJOIN(";",TRUE,IFERROR(INDEX($F$7:$F$12,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($F$7:$F$12,$F$7:$F$12,0),(ROW($B$7:$B$12)-ROW($B$7)+1))^0*COUNTIF($F$7:$F$12,$F$7:$F$12)=MAX(COUNTIF($F$7:$F$12,$F$7:$F$12)))*(ROW($B$7:$B$12)-ROW($B$7)+1))),ROW($H$1:H10))),""))
C3	C3	=TEXTJOIN(";",TRUE,IFERROR(INDEX($G$7:$G$12,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($G$7:$G$12,$G$7:$G$12,0),(ROW($B$7:$B$12)-ROW($B$7)+1))^0*COUNTIF($G$7:$G$12,$G$7:$G$12)=MAX(COUNTIF($G$7:$G$12,$G$7:$G$12)))*(ROW($B$7:$B$12)-ROW($B$7)+1))),ROW($H$1:H10))),""))
C4	C4	=TEXTJOIN(";",TRUE,IFERROR(INDEX($E$7:$E$12,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH($E$7:$E$12,$E$7:$E$12,0),(ROW($B$7:$B$12)-ROW($B$7)+1))^0*COUNTIF($E$7:$E$12,$E$7:$E$12)=MAX(COUNTIF($E$7:$E$12,$E$7:$E$12)))*(ROW($B$7:$B$12)-ROW($B$7)+1))),ROW($H$1:H10))),""))

 

[CA_Punit]

This is because you have selected range which is $B$7:$B$130 where count of Blank is More than the Maximum count. Also you cannot keep any Blank cell in between Ranges. So Let me come out with new formula considering the new scenerio

 

[Fluff]

Thanks for that, borrowing @Bo_Ry formula how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1		RSI Activities and Training WEST - Demographic
2		Summary
3
4		Most Attended Activity:	Cooking, Exercise class, Cinema	Number of Females:		2
5		Most Common Housing Status:	Social , PRS	Number of Males:		3
6		Services Most Known To:	RSI , None	Number of Transgender:		1
7		Age Range Most Attended:	18 - 25 , 26 - 35
8
9		Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To	Date
10		Cooking		Transgender	18 - 25	Social	RSI
11		Cooking		Male	18 - 25	Social	RSI
12		Cooking		Female	26 - 35	Social	RSI
13		Exercise class		Male	26 - 35	PRS	None
14		Exercise class		Male	18 - 25	PRS	None
15		Exercise class		Female	26 - 35	PRS	None
16		Cinema
17		Cinema
18		Cinema
19
Master

Cell Formulas
Range	Formula
F4	F4	=IFERROR(1/(1/COUNTIFS(D10:D190,"Female")),"")
F5	F5	=IFERROR(1/(1/COUNTIFS(D10:D190,"male")),"")
F6	F6	=IFERROR(1/(1/COUNTIFS(D10:D190,"transgender")),"")
C4	C4	=TEXTJOIN(", ",,INDEX(B10:B180,MODE.MULT(IF(B10:B180<>"",MATCH(B10:B180,B10:B180,)))))
C5	C5	=TEXTJOIN(", ",,INDEX(F10:F180,MODE.MULT(IF(F10:F180<>"",MATCH(F10:F180,F10:F180,)))))
C6	C6	=TEXTJOIN(", ",,INDEX(G10:G180,MODE.MULT(IF(G10:G180<>"",MATCH(G10:G180,G10:G180,)))))
C7	C7	=TEXTJOIN(", ",,INDEX(E10:E180,MODE.MULT(IF(E10:E180<>"",MATCH(E10:E180,E10:E180,)))))

 

[CA_Punit]

This this with blank cells also

cc.xls
	B	C	D	E	F	G	H	I
1	RSI Activities and Training WEST - Demographic
2	Summary
3
4	Most Attended Activity:	Cooking	Number of Female
5	Most Common Housing Status:		Number of Males:
6	Services Most Known To:		Number of Transgender:
7	Age Range Most Attended:
8
9	Type of Activity	Attendance Number	Gender	Age Range	Housing Status	Services Known To	Date
10	Cooking		Transgender	18 - 25	Social	RSI
11	Cooking		Male	18 - 25	Social	RSI
12	Cooking		Female	26 - 35	Social	RSI
13	Exercise class		Male	26 - 35	PRS	None
14	Exercise class		Male	18 - 25	PRS	None
15			Female	26 - 35	PRS	None
16
17	Cinema
18	Cinema
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
C4	C4	=TEXTJOIN(",",TRUE,IFERROR(INDEX($B$10:$B$55,AGGREGATE(15,6,1/(1/(FREQUENCY(MATCH(IF($B$10:$B$55<>"",$B$10:$B$55,0),IF($B$10:$B$55<>"",$B$10:$B$55,0),0),(ROW($B$10:$B$55)-ROW($B$10)+1))/($B$10:$B$55<>"")*(COUNTIF($B$10:$B$55,$B$10:$B$55)=MAX(COUNTIF($B$10:$B$55,$B$10:$B$55)))*(ROW($B$10:$B$55)-ROW($B$10)+1)))^0*(ROW($B$10:$B$55)-ROW($B$10)+1),(ROW($B$10:$B$55)-ROW($B$10)+1))),""))

 

[CA_Punit]

MY formula has turned out to be too long.. I missed @Fluff Formula..


I Missed out that MODE.MULT Ignores Text. Thanks again

 

[SaraWitch]

Amazing! @Fluff, you're formula works a treat - thank you so much!

And thank you also @CA_Punit for your time...

 

[SaraWitch]

Oh no! So I've sent it back to my work machine and this formula doesn't work in Excel 2010! Just as I thought we were there!

 

[Fluff]

2010 does not have Textjoin, so we can use Bo_Ry's other formula like
=INDEX(B10:B20,MIN(MODE.MULT(IF(B10:B20<>"",MATCH(B10:B20,B10:B20,)))))&IFERROR(", "&INDEX(B10:B20,SMALL(MODE.MULT(IF(B10:B20<>"",MATCH(B10:B20,B10:B20,))),2)),"")&IFERROR(", "&INDEX(B10:B20,SMALL(MODE.MULT(IF(B10:B20<>"",MATCH(B10:B20,B10:B20,))),3)),"")

 

[SaraWitch]

That works perfectly, thank you! I tried Bo_Ry's, but forgot about the blanks!

Thank you one and all!