Return most common, 2nd most common, 3rd most common, etc. text string in an array

bdkyzer

New Member
Joined
Oct 26, 2012
Messages
4
I have a table of data [Table_CQI_Contacts_be.accdb] of which one column [Provider Name] has text strings. My goal is to write a formula that looks in this column of data and returns the following:

Most Common Text String
2nd Most Common Text String
3rd Most Common Text String
.
.
.
20th Most Common Text String

Sometimes the column contains a blank field some I've incorporated a conditional IF statement using the ISBLANK function. To try and get to the most common text string I've been trying to use the MODE or LARGE functions but to no avail. I think the MODE.MULT function in EXCEL 2010 is exactly what I"m looking for but I'm in EXCEL 2007.

Here is the arrary formula that I"ve been trying to use thus far and it does work for finding the Most Common Text String, but I can't get it to work for the 2nd, 3rd, etc.

Code:
=IF(ISBLANK(Table_CQI_Contacts_be.accdb[Provider Name]),"",INDEX(Table_CQI_Contacts_be.accdb[Provider Name],MATCH(LARGE(COUNTIF(Table_CQI_Contacts_be.accdb[Provider Name],Table_CQI_Contacts_be.accdb[Provider Name]),large_seq_count),COUNTIF(Table_CQI_Contacts_be.accdb[Provider Name],Table_CQI_Contacts_be.accdb[Provider Name]),0)))

The named range [large_seq_count] is an integer listing 1,2,3,4...20 for the formula to reference when fulfilling the (k) component of the LARGE formula.

Again, I'm running EXCEL 2007 and any help is much appreciated.
 
This is a better example of Post#10

Note the change in B2, +((1/ROW())/10)

Excel Workbook
ABCDE
1Data Set:HelperNameOccurrences
2Harmon Harmon3
3SmithSmith3
4SmithWilliams3
5Harmon
6Harmon3.0167
7Williams
8Smith3.0125
9Williams
10Williams3.0100
11
Sheet1
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try this...

Sheet1

ABC

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]Harmon[/TD]
[TD="align: right"] _____ [/TD]
[TD="align: right"]Williams[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]Smith[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Smith[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]Smith[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Harmon[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]Harmon[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]Smith[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Enter this array formula** in C2:

=IFERROR(INDEX(A2:A10,MODE(MATCH(A2:A10,A2:A10,0)+{0,0})),"")

Enter this array formula** in C3 and copy down until you get blanks:

=IFERROR(INDEX(A$2:A$10,MODE(IF(COUNTIF(C$2:C2,A$2:A$10)=0,MATCH(A$2:A$10,A$2:A$10,0)+{0,0}))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hey T. Valco,
I've been trying to replicate your solution above but only include names where B is TRUE and am not having any luck. any assistance would be fantastic.
Using excel 2010

ABC

<tbody>
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]Harmon[/TD]
[TD="align: right"] TRUE [/TD]
[TD="align: right"] _____ [/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]Smith[/TD]
[TD="align: right"] TRUE [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]Smith[/TD]
[TD="align: right"] TRUE [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]Harmon[/TD]
[TD="align: right"] TRUE [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"] FALSE [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"] FALSE [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]Smith[/TD]
[TD="align: right"] TRUE [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"] FALSE [/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: right"]Williams[/TD]
[TD="align: right"] TRUE [/TD]
[TD="align: right"][/TD]

</tbody>

Cheers
 
Last edited:
Upvote 0
Hi T Valko - thanks for the Array formula, I think this is the start of what I need & I was wondering if you might be able to help as I am having difficulty figuring this out. I have two worksheets: Sheet 1 has a list of names in Col A and Sheet 2 has a very long list with the same names (many listed multiple times) and Col B has different values. I would like to map the most commonly occurring value in Col B for each of the values in Col A to each of their respective names in Col A in Sheet A. The example below might be more clear:[TABLE="width: 796"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Desired Results:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col A[/TD]
[TD][/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD][/TD]
[TD]Col A[/TD]
[TD] Col B[/TD]
[TD] Col C[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]white[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD] yellow[/TD]
[TD] white[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD] black[/TD]
[TD] black (or "")[/TD]
[/TR]
[TR]
[TD]Laura[/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]black[/TD]
[TD][/TD]
[TD]Laura[/TD]
[TD] blue[/TD]
[TD] red (doesn't matter the order of these since same # of occurences)[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD] green[/TD]
[TD] blue[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]black[/TD]
[TD][/TD]
[TD]Steve[/TD]
[TD] green[/TD]
[TD] green (or "")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Laura[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Steve[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]white[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Laura[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm not really concerned with how many times or which names in Col A are the most commonly repeated, but rather which values in Col B are the most commonly occurring for each of the names in Col A (if that makes sense....)

Thank you in advance!!
Vanessa
 
Last edited:
Upvote 0
Hi Vanessa

Maybe this

Sheet2
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD]
[TD]
B
[/TD]
[/TR]
[TR]
[TD]
1
[/TD]
[TD]
Name​
[/TD]
[TD]
Color​
[/TD]
[/TR]
[TR]
[TD]
2
[/TD]
[TD]
Mary​
[/TD]
[TD]
white​
[/TD]
[/TR]
[TR]
[TD]
3
[/TD]
[TD]
Mary​
[/TD]
[TD]
yellow​
[/TD]
[/TR]
[TR]
[TD]
4
[/TD]
[TD]
Joe​
[/TD]
[TD]
black​
[/TD]
[/TR]
[TR]
[TD]
5
[/TD]
[TD]
Bill​
[/TD]
[TD]
blue​
[/TD]
[/TR]
[TR]
[TD]
6
[/TD]
[TD]
Joe​
[/TD]
[TD]
black​
[/TD]
[/TR]
[TR]
[TD]
7
[/TD]
[TD]
Bill​
[/TD]
[TD]
green​
[/TD]
[/TR]
[TR]
[TD]
8
[/TD]
[TD]
Bill​
[/TD]
[TD]
green​
[/TD]
[/TR]
[TR]
[TD]
9
[/TD]
[TD]
Bill​
[/TD]
[TD]
green​
[/TD]
[/TR]
[TR]
[TD]
10
[/TD]
[TD]
Laura​
[/TD]
[TD]
blue​
[/TD]
[/TR]
[TR]
[TD]
11
[/TD]
[TD]
Steve​
[/TD]
[TD]
green​
[/TD]
[/TR]
[TR]
[TD]
12
[/TD]
[TD]
Mary​
[/TD]
[TD]
yellow​
[/TD]
[/TR]
[TR]
[TD]
13
[/TD]
[TD]
Joe​
[/TD]
[TD]
black​
[/TD]
[/TR]
[TR]
[TD]
14
[/TD]
[TD]
Joe​
[/TD]
[TD]
black​
[/TD]
[/TR]
[TR]
[TD]
15
[/TD]
[TD]
Mary​
[/TD]
[TD]
white​
[/TD]
[/TR]
[TR]
[TD]
16
[/TD]
[TD]
Laura​
[/TD]
[TD]
red​
[/TD]
[/TR]
[TR]
[TD]
17
[/TD]
[TD]
Mary​
[/TD]
[TD]
yellow​
[/TD]
[/TR]
</tbody>[/TABLE]



Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD]
[TD]
B
[/TD]
[TD]
C
[/TD]
[/TR]
[TR]
[TD]
1
[/TD]
[TD]
Name​
[/TD]
[TD]
Color1​
[/TD]
[TD]
Color2​
[/TD]
[/TR]
[TR]
[TD]
2
[/TD]
[TD]
Mary​
[/TD]
[TD]
yellow​
[/TD]
[TD]
white​
[/TD]
[/TR]
[TR]
[TD]
3
[/TD]
[TD]
Joe​
[/TD]
[TD]
black​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4
[/TD]
[TD]
Laura​
[/TD]
[TD]
blue​
[/TD]
[TD]
red​
[/TD]
[/TR]
[TR]
[TD]
5
[/TD]
[TD]
Bill​
[/TD]
[TD]
green​
[/TD]
[TD]
blue​
[/TD]
[/TR]
[TR]
[TD]
6
[/TD]
[TD]
Steve​
[/TD]
[TD]
green​
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in B2 copied across and down
=IFERROR(INDEX(Sheet2!$B$2:$B$17,MATCH(LARGE(IF(Sheet2!$A$2:$A$17=$A2,IF(ISNA(MATCH(Sheet2!$B$2:$B$17,$A2:A2,0)),MATCH(Sheet2!$B$2:$B$17,Sheet2!$B$2:$B$17,0))),1),IF(Sheet2!$A$2:$A$17=$A2,IF(ISNA(MATCH(Sheet2!$B$2:$B$17,$A2:A2,0)),MATCH(Sheet2!$B$2:$B$17,Sheet2!$B$2:$B$17,0))),0)),"")

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 
Upvote 0
Thank you so much!! This is excellent, works like a charm. I really appreciate the quick reply, too!!

Cheers!
Vanessa
 
Upvote 0
I have a similar issue I would like help solving, I have a set of data in one sheet that covers 3 Fiscal years FY15, FY16 and FY17 (named Open_FY) and sub categories of issues (named SubCat) this are workplace issues like Hostile Work Environment, Dignity and Respect, Theft... so in another sheet I layout the data in tables and charts and one table is the top 5 subcategories by Open_FY...

I can get the Top 5 for the over all date with this formula: =INDEX(SubCat,MATCH(LARGE(FREQUENCY(MATCH(SubCat,SubCat,0),ROW(SubCat)-ROW('FY16-FY17 Recieved'!$M$1)+1)+ROW('Prepared Foods FY16-FY17.xlsx'!SubCat_Plus1)/10000,ROWS(R$109:R109)),FREQUENCY(MATCH(SubCat,SubCat,0),ROW(SubCat)-ROW('FY16-FY17 Recieved'!$M$1)+1)+ROW(SubCat_Plus1)/10000,0)))

But can't find a way to get the correct Top 5 by Open_FY, an ideas? Thank you
 
Upvote 0
Welcome to Mr Excel forum

This is an old thread.
To improve your chances of getting help you should create a new thread including a small data sample and expected results.

M.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

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