Find the most frequent and second most frequent text string whilst ignoring numbers

Hi All,

I’m trying to identify the most frequent occurring text string in a row followed by the second most frequent text string whilst ignoring any cells in the row that contain numerical values.

As an example, I have a table of sales results (attached) by week by region. Each week, the top salesperson and number of sales are updated into the applicable row for their region.

Sales.xlsx

Through the forum pages I have been able to identify the formula for determining the most frequent text string in a row using an array formula –

= (INDEX(F6:Q6,MODE(IF(F6:Q6<>"",MATCH(F6:Q6,F6:Q6,0))))

However,
1) I am unsure how to find the second most frequently occurring text string in a row, and
2) Depending on the frequency of sales each week, it is possible the array formula will return the most frequently occurring numerical value (sales volume), however, I would like to ignore the numerical value and only return the text string value (for both the most frequent and second most frequent values)

I understand that if there is a tie the array formula will provide the first most frequent occurring text string, spread sheet attached is for example only.

Thanks for your help
First time poster so please let me know if you require any additional information.

Thanks,

You could use MODE.MULT() in 2007 and above, unfortunately I only have 2003 available at the moment, so I can't test an example for you.
Try googling "mode.mult excel function" to find examples
MODE.MULT function is the official MS site.

Note on your current workbook, you might be safer using these array formulae.
In C6

=INDEX(F6:Q6,1,MODE(IF(ISTEXT(F6:Q6),MATCH(F6:Q6,F6:Q6,0))))

and in C7

=INDEX(F7:Q7,1,MODE(IF(ISNUMBER(F7:Q7),MATCH(F7:Q7,F7:Q7,0))))

Hmm?
I can't see a clear solution using MODE(), and I'm not entirely sure it is the best approach.

In addition to the previous post ...
This array in D7

=INDEX(F6:Q6,1,MODE(IF(--(ISTEXT(F6:Q6))*--(F6:Q6<>C6),MATCH(F6:Q6,F6:Q6,0))))

and this array in D8

=INDEX(F7:Q7,1,MODE(IF(--(ISNUMBER(F7:Q7))*--(F7:Q7<>C7),MATCH(F7:Q7,F7:Q7,0))))

However MODE() will not recognise unique cell values, i.e. the values will have to appear more than once to be recognised.
If there is no duplication returned MODE() will return #N/A.

This isn't perhaps ideal, but might well serve your purpose.

To better understand the returned result, change the value in row 7 (yellow cells) to either 11, or 15.
See this workbook