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!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,774
something like this?

Column1Count
Cooking3
Exercise class3
Male3
18 - 252
26 - 352
46 and over2
Female2
Homeworks2
PRS2
RSI2
Rough sleeping2
HF1
None1
RRP1
TA1
Transgender1
 

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Similar, although I can use a filter for this because I do have quite a few DDLs. I could record all this on a separate page, but I just wondered if there was a way to show in the summary box that cooking and exercise class were most attended, e.g. Most Attended Activity: Cooking, Exercise Class...?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,774
sure, like this?
Column1Count
Cooking3
Exercise class3
Male3


anyway, you can filter table from post#2 as you wish to get what you want
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,774

ADVERTISEMENT

or

MaxTextMax
3Cooking, Exercise class, Male
218 - 25, 26 - 35, 46 and over, Female, Homeworks, PRS, RSI, Rough sleeping
1HF, None, RRP, TA, Transgender
 

SaraWitch

New Member
Joined
Sep 29, 2015
Messages
49
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
That's great and I have added a separate summary page; however, I wanted this summary box to look like this (I have over typed the formulas, so refer to my first posting for these (order rearranged, but formulas the same!)):
Most Attended Activity:Cooking, Exercise classNumber of Females:2
Most Common Housing Status:PRS, Rough sleepingNumber of Males:3
Services Most Known To:RSI, HomeworksNumber of Transgender:1
Age Range Most Attended:18 - 25, 26 - 35
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
 

CA_Punit

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

ADVERTISEMENT

Here you can get the Name Appearing in Column H1 and H2,

Book1
ABCDEFGH
1Most Attended ActivityCookingAge Range Most Attended18 - 25Cooking
2Number of Females2Most Common Housing StatusPRS 
3Number of Males3Services Most Known ToRSI 
4Number of Transgender1 
5 
6Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known To 
7Cooking Transgender 18 - 25 PRS RSI
8Cooking Male 18 - 25 PRS RSI
9Cooking Female 26 - 35 Rough sleeping Homeworks
10Cooking Male 26 - 35 Rough sleeping Homeworks
11Exercise class Male 46 and over TA RRP
12Exercise class Female 46 and over HF None
13
14
Sheet1
Cell Formulas
RangeFormula
H1:H6H1=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))),ROWS($G$1:G1))),"")
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
874
Office Version
  1. 365
Platform
  1. Windows
Oh Sorry I didnt See the Post 6, Let me again give a try as per your requirement
 

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
113
Office Version
  1. 365
Platform
  1. Windows
For MS 365
B2
=TEXTJOIN(", ",,INDEX(A7:A12,MODE.MULT(MATCH(A7:A12,A7:A12,))))

For other versions
=INDEX(A7:A12,MIN(MODE.MULT(MATCH(A7:A12,A7:A12,))))&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),2)),"")&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),3)),"")

Book1
ABCDEFGHIJ
1Most Attended ActivityCooking, Exercise classAge Range Most Attended 18 - 25 , 26 - 35 , 46 and over Cooking, Exercise class 18 - 25 , 26 - 35 , 46 and over
2Number of Females2Most Common Housing StatusPRS
3Number of Males3Services Most Known ToRSI
4Number of Transgender1
5
6Type of ActivityAttendance NumberGenderAge RangeHousing StatusServices Known To
7Cooking Transgender 18 - 25 PRS RSI
8Cooking Male 18 - 25 PRS RSI
9Cooking Female 26 - 35 Rough sleeping Homeworks
10Exercise class Male 26 - 35 Rough sleeping Homeworks
11Exercise class Male 46 and over TA RRP
12Exercise class Female 46 and over HF None
Sheet1
Cell Formulas
RangeFormula
B1,E1B1=TEXTJOIN(", ",,INDEX(A7:A12,MODE.MULT(MATCH(A7:A12,A7:A12,))))
G1,J1G1=INDEX(A7:A12,MIN(MODE.MULT(MATCH(A7:A12,A7:A12,))))&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),2)),"")&IFERROR(", "&INDEX(A7:A12,SMALL(MODE.MULT(MATCH(A7:A12,A7:A12,)),3)),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,176
Messages
5,527,255
Members
409,754
Latest member
ekTZ

This Week's Hot Topics

Top