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

[bdkyzer]

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.

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

 

[Marcol]

This is a better example of Post#10

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

Excel Workbook
	A	B	C	D	E
1	Data Set:	Helper		Name	Occurrences
2	Harmon			Harmon	3
3	Smith			Smith	3
4	Smith			Williams	3
5	Harmon
6	Harmon	3.0167
7	Williams
8	Smith	3.0125
9	Williams
10	Williams	3.0100
11
Sheet1

 

[T. Valko]

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

High 5!

 

[poolio]

High 5!

** Smack **

 

[ArkAngell]

Try this...

Sheet1

	A	B	C

<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

	A	B	C

<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

 

[Vanessa17]

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

 

[Marcelo Branco]

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.

 

[Vanessa17]

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

Cheers!
Vanessa

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Edward_Les]

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

 

[Marcelo Branco]

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.