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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,441
Office Version
  1. 365
Platform
  1. Windows
@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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Sorry; here you go:
RSI Activities WEST.xlsm
ABCDEFGH
1RSI Activities and Training WEST - Demographic
2Summary
3
4Most Attended Activity: Number of Females:2
5Most Common Housing Status: Number of Males:3
6Services Most Known To: Number of Transgender:1
7Age Range Most Attended: 
8
9Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known ToDate
10Cooking Transgender 18 - 25 Social RSI
11Cooking Male 18 - 25 Social RSI
12Cooking Female 26 - 35 Social RSI
13Exercise class Male 26 - 35 PRS None
14Exercise class Male 18 - 25 PRS None
15Exercise class Female 26 - 35 PRS None
16Cinema
17Cinema
18Cinema
19
Demographics TEST (2)
Cell Formulas
RangeFormula
F4F4=IF(COUNTIF($D$10:$D$129,"Female")=0,"",COUNTIF($D$10:$D$129,"Female"))
F5F5=IF(COUNTIF($D$10:$D$129,"Male")=0,"",COUNTIF($D$10:$D$129,"Male"))
F6F6=IF(COUNTIF($D$10:$D$129,"Transgender")=0,"",COUNTIF($D$10:$D$129,"Transgender"))
C4C4=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))),""))
C5C5=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))),""))
C6C6=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))),""))
C7C7=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
ABCDEFGH
1Most Attended Activity: Number of Females:2
2Most Common Housing Status:Social;PRSNumber of Males:3
3Services Most Known To:RSI;NoneNumber of Transgender:1
4Age Range Most Attended:18 - 25;26 - 35
5
6Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known ToDate
7Cooking Transgender 18 - 25 Social RSI
8Cooking Male 18 - 25 Social RSI
9Cinema Female 26 - 35 Social RSI
10Exercise class Male 26 - 35 PRS None
11Exercise class Male 18 - 25 PRS None
12Exercise class Female 26 - 35 PRS None
13
Demographics TEST
Cell Formulas
RangeFormula
F1F1=IF(COUNTIF($D$7:$D$126,"Female")=0,"",COUNTIF($D$7:$D$126,"Female"))
F2F2=IF(COUNTIF($D$7:$D$126,"Male")=0,"",COUNTIF($D$7:$D$126,"Male"))
F3F3=IF(COUNTIF($D$7:$D$126,"Transgender")=0,"",COUNTIF($D$7:$D$126,"Transgender"))
C1C1=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))),""))
C2C2=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))),""))
C3C3=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))),""))
C4C4=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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,441
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, borrowing @Bo_Ry formula how about
+Fluff New.xlsm
ABCDEFGH
1RSI Activities and Training WEST - Demographic
2Summary
3
4Most Attended Activity:Cooking, Exercise class, CinemaNumber of Females:2
5Most Common Housing Status:Social , PRSNumber of Males:3
6Services Most Known To:RSI , NoneNumber of Transgender:1
7Age Range Most Attended:18 - 25 , 26 - 35
8
9Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known ToDate
10CookingTransgender18 - 25SocialRSI
11CookingMale18 - 25SocialRSI
12CookingFemale26 - 35SocialRSI
13Exercise classMale26 - 35PRSNone
14Exercise classMale18 - 25PRSNone
15Exercise classFemale26 - 35PRSNone
16Cinema
17Cinema
18Cinema
19
Master
Cell Formulas
RangeFormula
F4F4=IFERROR(1/(1/COUNTIFS(D10:D190,"Female")),"")
F5F5=IFERROR(1/(1/COUNTIFS(D10:D190,"male")),"")
F6F6=IFERROR(1/(1/COUNTIFS(D10:D190,"transgender")),"")
C4C4=TEXTJOIN(", ",,INDEX(B10:B180,MODE.MULT(IF(B10:B180<>"",MATCH(B10:B180,B10:B180,)))))
C5C5=TEXTJOIN(", ",,INDEX(F10:F180,MODE.MULT(IF(F10:F180<>"",MATCH(F10:F180,F10:F180,)))))
C6C6=TEXTJOIN(", ",,INDEX(G10:G180,MODE.MULT(IF(G10:G180<>"",MATCH(G10:G180,G10:G180,)))))
C7C7=TEXTJOIN(", ",,INDEX(E10:E180,MODE.MULT(IF(E10:E180<>"",MATCH(E10:E180,E10:E180,)))))
 

CA_Punit

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

ADVERTISEMENT

This this with blank cells also

cc.xls
BCDEFGHI
1RSI Activities and Training WEST - Demographic
2Summary
3
4Most Attended Activity:CookingNumber of Female
5Most Common Housing Status:Number of Males:
6Services Most Known To:Number of Transgender:
7Age Range Most Attended:
8
9Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known ToDate
10Cooking Transgender 18 - 25 Social RSI
11Cooking Male 18 - 25 Social RSI
12Cooking Female 26 - 35 Social RSI
13Exercise class Male 26 - 35 PRS None
14Exercise class Male 18 - 25 PRS None
15 Female 26 - 35 PRS None
16
17Cinema
18Cinema
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1
Cell Formulas
RangeFormula
C4C4=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

Well-known Member
Joined
Nov 18, 2019
Messages
874
Office Version
  1. 365
Platform
  1. Windows
MY formula has turned out to be too long.. I missed @Fluff Formula..


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

SaraWitch

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

ADVERTISEMENT

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

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

:):):)
 

SaraWitch

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,441
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
That works perfectly, thank you! I tried Bo_Ry's, but forgot about the blanks!

Thank you one and all!
 

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