Frequent Text - Duplicates

SaraWitch

New Member
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

Ta muchly!

CA_Punit

Well-known Member

Book1
ABCDEF
1Most Attended ActivityCooking;Exercise classNumber of Female2
2Most Common Housing Status PRS Number of Male3
3Services Most Known To RSI Number of Transgender1
4Most Attended Age Range 18 - 25
5
6Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known To
7CookingTransgender 18 - 25 PRS RSI
8CookingMale 18 - 25 PRS RSI
9CookingFemale 26 - 35 Rough sleeping RSI
10Exercise classMale 26 - 35 Rough sleeping Homeworks
11Exercise classMale 18 - 25 PRS RRP
12Exercise classFemale 46 and over HF None
13
14
15
Sheet1
Cell Formulas
RangeFormula
E1:E3E1=COUNTIF(\$C\$7:\$C\$12,MID(C1,SEARCH(" ",C1,8)+1,50))
B1B1=TEXTJOIN(";",TRUE,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))),ROW(\$G\$1:G10))),""))
B2B2=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(\$A\$7:\$A\$12)-ROW(\$A\$7)+1))^0*COUNTIF(\$E\$7:\$E\$12,\$E\$7:\$E\$12)=MAX(COUNTIF(\$E\$7:\$E\$12,\$E\$7:\$E\$12)))*(ROW(\$A\$7:\$A\$12)-ROW(\$A\$7)+1))),ROW(\$G\$1:G10))),""))
B3B3=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(\$A\$7:\$A\$12)-ROW(\$A\$7)+1))^0*COUNTIF(\$F\$7:\$F\$12,\$F\$7:\$F\$12)=MAX(COUNTIF(\$F\$7:\$F\$12,\$F\$7:\$F\$12)))*(ROW(\$A\$7:\$A\$12)-ROW(\$A\$7)+1))),ROW(\$G\$1:G10))),""))
B4B4=TEXTJOIN(";",TRUE,IFERROR(INDEX(\$D\$7:\$D\$12,AGGREGATE(15,6,1/(1/((FREQUENCY(MATCH(\$D\$7:\$D\$12,\$D\$7:\$D\$12,0),(ROW(\$A\$7:\$A\$12)-ROW(\$A\$7)+1))^0*COUNTIF(\$D\$7:\$D\$12,\$D\$7:\$D\$12)=MAX(COUNTIF(\$D\$7:\$D\$12,\$D\$7:\$D\$12)))*(ROW(\$A\$7:\$A\$12)-ROW(\$A\$7)+1))),ROW(\$G\$1:G10))),""))

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

CA_Punit

Well-known Member
@Bo_Ry

I never Tried using Mode.Mult. Thanks you for this

SaraWitch

New Member
You are a whizz! Thank you, Bo-Ry!

SaraWitch

New Member
And thank you too, CA-Punit!

SaraWitch

New Member

Sorry, me again! None of the formulas seem to be returning any results for me. I example Bo_Ry's MS365:
 Most Attended Activity: Most Common Housing Status: Services Most Known To: Age Range Most Attended: Type of Activity Attendance Number Cooking Cooking Cooking Exercise class Exercise class Exercise class

Fluff

MrExcel MVP, Moderator
Rather than posting a table, check the Cells formulas box & then Generate Output, rather than the Table Only Output

CA_Punit

Well-known Member

and confirm the formula with Ctrl+Shift+Enter and Not Just Enter

SaraWitch

New Member
Sorry, profile now updated. I'm using two systems (Microsoft 365 at home and Office 2007 on a work laptop), but am today using 365. Yes, I did press Ctrl+Shift+Enter (although I did try just Enter when this didn't work!).

CA_Punit

Well-known Member
If you are using office 365 no need to enter Ctrl+Shift+Enter. Use result of Post 11 and Try

SaraWitch

New Member
Good morning.
So it works if the table is in row 1.
 Most Attended Activity: Cooking;Exercise class Number of Females: 2 Most Common Housing Status: Social;PRS Number of Males: 3 Services Most Known To: RSI;None 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 Date Cooking Transgender 18 - 25 Social RSI Cooking Male 18 - 25 Social RSI Cooking Female 26 - 35 Social RSI Exercise class Male 26 - 35 PRS None Exercise class Male 18 - 25 PRS None Exercise class Female 26 - 35 PRS None Cinema Cinema Cinema

However, I have a heading and as soon as I add this (and change the criteria as appropriate), it doesn't work...?
 RSI Activities and Training WEST - Demographic Summary Most Attended Activity: Number of Females: 2 Most Common Housing Status: Number of Males: 3 Services Most Known To: Number of Transgender: 1 Age Range Most Attended: Type of Activity Attendance Number Gender Age Range Housing Status Services Known To Date Cooking Transgender 18 - 25 Social RSI Cooking Male 18 - 25 Social RSI Cooking Female 26 - 35 Social RSI Exercise class Male 26 - 35 PRS None Exercise class Male 18 - 25 PRS None Exercise class Female 26 - 35 PRS None Cinema Cinema Cinema

I'm clearly missing something obvious, but just can't see it!

Replies
2
Views
44
Replies
5
Views
58
Replies
11
Views
244
Replies
2
Views
70
Replies
1
Views
1K