Frequent Text - Duplicates

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
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!
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
874
Office Version
  1. 365
Platform
  1. Windows
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))),""))
 

Some videos you may like

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
Joined
Nov 18, 2019
Messages
874
Office Version
  1. 365
Platform
  1. Windows
@Bo_Ry

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

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You are a whizz! Thank you, Bo-Ry! :):):)
 

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,441
Office Version
  1. 365
Platform
  1. Windows
Rather than posting a table, check the Cells formulas box & then Generate Output, rather than the Table Only Output
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
874
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which version of Excel Do you Use?? Please update your profile
and confirm the formula with Ctrl+Shift+Enter and Not Just Enter
 

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
874
Office Version
  1. 365
Platform
  1. Windows
If you are using office 365 no need to enter Ctrl+Shift+Enter. Use result of Post 11 and Try
 

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,189
Messages
5,527,302
Members
409,758
Latest member
Smith79

This Week's Hot Topics

Top