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