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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

poolio

New Member
Joined
Oct 8, 2010
Messages
41
This may be a tad more basic than you want...

I would have a 2nd column that had a COUNTIF which referenced the first column, probably with an OFFSET (looking back up the list to ensure that you weren't capturing the same phrase twice), then in a 3rd column use the LARGE function to sort the most commonly used phrases... with a VLOOKUP to the phrase next to the largest value (you will need to add a very small number to all of your COUNTIF values to prevent LARGE / VLOOKUP repeating the same phrase twice)
JP
 

bdkyzer

New Member
Joined
Oct 26, 2012
Messages
4
This may be a tad more basic than you want...

I would have a 2nd column that had a COUNTIF which referenced the first column, probably with an OFFSET (looking back up the list to ensure that you weren't capturing the same phrase twice), then in a 3rd column use the LARGE function to sort the most commonly used phrases... with a VLOOKUP to the phrase next to the largest value (you will need to add a very small number to all of your COUNTIF values to prevent LARGE / VLOOKUP repeating the same phrase twice)
JP
JP, I like where you are going, but I'm not following for some reason. Can you generate a small sample worksheet using the following names in one column?

Data Set:
Harmon
Smith
Smith
Harmon
Williams
Williams
Smith
Williams
Williams

Thanks!!
 

poolio

New Member
Joined
Oct 8, 2010
Messages
41
BD,

I've produced a sheet, but can't attach (am I missing something?!)

Anyway here it is with the equations expanded... these equations could be combined, but I've left them apart for ease of understanding...

CountIFOffset Check+ Small NoVLOOKUPLargeName
1Harmon=IF(D2>1,0,COUNTIF(B:B,B2))=COUNTIF(OFFSET($B$2,0,0,A2,1),B2)=C2+A2/1000=B2=LARGE($E$2:$E$10,A2)=VLOOKUP(G2,$E$2:$F$10,2,0)
2Smith=IF(D3>1,0,COUNTIF(B:B,B3))=COUNTIF(OFFSET($B$2,0,0,A3,1),B3)=C3+A3/1000=B3=LARGE($E$2:$E$10,A3)=VLOOKUP(G3,$E$2:$F$10,2,0)
3Smith=IF(D4>1,0,COUNTIF(B:B,B4))=COUNTIF(OFFSET($B$2,0,0,A4,1),B4)=C4+A4/1000=B4=LARGE($E$2:$E$10,A4)=VLOOKUP(G4,$E$2:$F$10,2,0)
4Harmon=IF(D5>1,0,COUNTIF(B:B,B5))=COUNTIF(OFFSET($B$2,0,0,A5,1),B5)=C5+A5/1000=B5=LARGE($E$2:$E$10,A5)=VLOOKUP(G5,$E$2:$F$10,2,0)
5Williams=IF(D6>1,0,COUNTIF(B:B,B6))=COUNTIF(OFFSET($B$2,0,0,A6,1),B6)=C6+A6/1000=B6=LARGE($E$2:$E$10,A6)=VLOOKUP(G6,$E$2:$F$10,2,0)
6Williams=IF(D7>1,0,COUNTIF(B:B,B7))=COUNTIF(OFFSET($B$2,0,0,A7,1),B7)=C7+A7/1000=B7=LARGE($E$2:$E$10,A7)=VLOOKUP(G7,$E$2:$F$10,2,0)
7Smith=IF(D8>1,0,COUNTIF(B:B,B8))=COUNTIF(OFFSET($B$2,0,0,A8,1),B8)=C8+A8/1000=B8=LARGE($E$2:$E$10,A8)=VLOOKUP(G8,$E$2:$F$10,2,0)
8Williams=IF(D9>1,0,COUNTIF(B:B,B9))=COUNTIF(OFFSET($B$2,0,0,A9,1),B9)=C9+A9/1000=B9=LARGE($E$2:$E$10,A9)=VLOOKUP(G9,$E$2:$F$10,2,0)
9Williams=IF(D10>1,0,COUNTIF(B:B,B10))=COUNTIF(OFFSET($B$2,0,0,A10,1),B10)=C10+A10/1000=B10=LARGE($E$2:$E$10,A10)=VLOOKUP(G10,$E$2:$F$10,2,0)

<tbody>
</tbody>

And how it would look... I would round up the results and add an IF<1,"" in the Large Name column to get rid of the repeats...

CountIFOffset Check
+ Small NoVLOOKUPLargeName
1Harmon212.001Harmon4.005Williams
2Smith313.002Smith3.002Smith
3Smith020.003Smith2.001Harmon
4Harmon020.004Harmon0.009Williams
5Williams414.005Williams0.008Williams
6Williams020.006Williams0.007Smith
7Smith030.007Smith0.006Williams
8Williams030.008Williams0.004Harmon
9Williams040.009Williams0.003Smith

<tbody>
</tbody>

Hope this helps...
JP
 

bdkyzer

New Member
Joined
Oct 26, 2012
Messages
4
JP, Thanks a bunch! I was able to do some combining of the formulas you've got here and I added on some date criteria that I wanted to incorporate as well so I modified a couple of your CountIf f(x)s to CountIfs. Overall, this strategy worked perfectly and gave me what I was looking for. Thanks a bunch for your assistance!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
JP, I like where you are going, but I'm not following for some reason. Can you generate a small sample worksheet using the following names in one column?

Data Set:
Harmon
Smith
Smith
Harmon
Williams
Williams
Smith
Williams
Williams

Thanks!!
Try this...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:72px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">Harmon</td><td style="color:#ffffff; text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Williams</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Smith</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Smith</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Smith</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Harmon</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Harmon</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Williams</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Williams</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Smith</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Williams</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">Williams</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></table>
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Or you can use a Pivot Table - very easy and efficient

Names ---> Row Labels
Names ---> Values area
Sort

Names
Counting
Williams
4
Smith
3
Harmon
2

<tbody>
</tbody>

M.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,328
As the data is in a database table, maybe just run a query against it. Set up via menu ALT-D-D-N and follow the wizard.

This database type approach might be preferred over formulas?

For counts of all data, SQL could be
Code:
SELECT [Provider Name], COUNT(*) AS [MyCount]
FROM Table_CQI_Contacts_be
GROUP BY [Provider Name]
If you only want the TOP n, where n is an integer, use instead
Code:
SELECT TOP n [Provider Name], COUNT(*) AS [MyCount]
FROM Table_CQI_Contacts_be
GROUP BY [Provider Name]
Or maybe only those with a count more than 10
Code:
SELECT [Provider Name], COUNT(*) AS [MyCount]
FROM Table_CQI_Contacts_be
GROUP BY [Provider Name]
HAVING COUNT(*) > 10

hth
 

poolio

New Member
Joined
Oct 8, 2010
Messages
41
JP, Thanks a bunch! I was able to do some combining of the formulas you've got here and I added on some date criteria that I wanted to incorporate as well so I modified a couple of your CountIf f(x)s to CountIfs. Overall, this strategy worked perfectly and gave me what I was looking for. Thanks a bunch for your assistance!
Yahay!!

After a couple of years of getting assistance from Mr Excel, so glad that for the first time I've been able to help someone else...
Just made my day, so glad I could help!

JP
 

Marcol

Well-known Member
Joined
Mar 1, 2010
Messages
644
Yet another way, introducing tie-breakers, therefore easily listing results when there are duplicate counts.

Excel Workbook
ABCDE
1Data Set:HelperNameOccurrences
2Harmon
 
Williams4
3SmithSmith3
4SmithHarmon2
5Harmon2.005
6Williams
7Williams
8Smith3.008
9Williams
10Williams4.010
11
12
13
14
15
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,095,654
Messages
5,445,765
Members
405,358
Latest member
Nabud

This Week's Hot Topics

Top