Frequent Text - Duplicates

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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 ActivityCookingAge Range Most Attended18 - 25
Number of Females2Most Common Housing StatusPRS
Number of Males3Services Most Known ToRSI
Number of Transgender1
Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices 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!
 
How about this

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))),""))
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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 ActivityAttendance Number
Cooking
Cooking
Cooking
Exercise class
Exercise class
Exercise class
 
Upvote 0
Rather than posting a table, check the Cells formulas box & then Generate Output, rather than the Table Only Output
 
Upvote 0
Which version of Excel Do you Use?? Please update your profile
and confirm the formula with Ctrl+Shift+Enter and Not Just Enter
 
Upvote 0
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!).
 
Upvote 0
If you are using office 365 no need to enter Ctrl+Shift+Enter. Use result of Post 11 and Try
 
Upvote 0
Good morning.
So it works if the table is in row 1.
Most Attended Activity:Cooking;Exercise classNumber of Females:2
Most Common Housing Status:Social;PRSNumber of Males:3
Services Most Known To:RSI;NoneNumber of Transgender:1
Age Range Most Attended:18 - 25;26 - 35
Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known ToDate
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 ActivityAttendance NumberGenderAge RangeHousing StatusServices Known ToDate
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! :unsure:
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top