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

B

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
poolio said:
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
Click to expand...

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

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

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

[TABLE="width: 446"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]CountIF[/TD]
[TD]Offset Check
[/TD]
[TD]+ Small No[/TD]
[TD]VLOOKUP[/TD]
[TD]Large[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Harmon[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2.001[/TD]
[TD]Harmon[/TD]
[TD]4.005[/TD]
[TD]Williams[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Smith[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3.002[/TD]
[TD]Smith[/TD]
[TD]3.002[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Smith[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0.003[/TD]
[TD]Smith[/TD]
[TD]2.001[/TD]
[TD]Harmon[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Harmon[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0.004[/TD]
[TD]Harmon[/TD]
[TD]0.009[/TD]
[TD]Williams[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Williams[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4.005[/TD]
[TD]Williams[/TD]
[TD]0.008[/TD]
[TD]Williams[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Williams[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0.006[/TD]
[TD]Williams[/TD]
[TD]0.007[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Smith[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]0.007[/TD]
[TD]Smith[/TD]
[TD]0.006[/TD]
[TD]Williams[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Williams[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]0.008[/TD]
[TD]Williams[/TD]
[TD]0.004[/TD]
[TD]Harmon[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Williams[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0.009[/TD]
[TD]Williams[/TD]
[TD]0.003[/TD]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]

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
bdkyzer said:
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!!
Click to expand...
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

[TABLE="width: 117"]
<tbody>[TR]
[TD="class: xl63, width: 79"]Names
[/TD]
[TD="class: xl63, width: 77"]Counting
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Williams
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Smith
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Harmon
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]

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
bdkyzer said:
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!
Click to expand...

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
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA to perform Text Diff (but with >2500 Char per cell)
Replies
0
Views
240
potatoooooooo
P
T
  • Solved
Return the top 10 occurrences of an item in a table and the count of that item
Replies
3
Views
613
Fluff
Fluff
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
480
felixstraube
F
C
  • Question Question
If 2 columns contain text from 2 ranges, insert text
Replies
4
Views
882
Peter_SSs
Peter_SSs
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
941
ahaye
A

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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