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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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...

Book1
ABC
2Harmon_Williams
3Smith_Smith
4Smith_Harmon
5Harmon__
6Williams__
7Williams__
8Smith__
9Williams__
10Williams__
Sheet1

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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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