Frequent Text - Duplicates

SaraWitch

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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,328
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
365, 2010
Platform
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,328
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,328

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
365, 2010
Platform
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
866
Office Version
365
Platform
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
866
Office Version
365
Platform
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
108
Office Version
365
Platform
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)),"")
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,107
Messages
5,509,259
Members
408,719
Latest member
padapinto

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top