get most frequent occuring text in Excel

Q89. In Excel, how can I get the most frequently occurring text in a range?

For example, I want to identify the most frequently occurring name in the range B2 :B21 in Screenshot below.

excel get most frequent occurring text

To do it in Excel, here is the answer:

a) Enter the formula =INDEX(B2:B21,MODE(MATCH(B2:B21,B2:B21,0))) to get the most frequently occuring text.

MATCH function matches the range against itself. Because the lookup value is an array and contains more than one value, MATCH returns an array of results.

Since MATCH always returns the first match, subsequent occurrences of the value will return the first match position (Col A in Screenshot below).

Match would return {1,2,2,1,2,3,3,4,5,3,6,6,5,6,5,3,6,5,6,4} (Col C in Screenshot below).

Mode then determines the most frequently returned position (6) (cell C22 in Screenshot below)

The value returned by Mode is then used to get the actual Text corresponding to position using the INDEX function.

excel excel get most frequent occurring text


You can find similar Excel Questions and Answer hereunder

1) Write to text file without quotes in vba in Excel

2) Vba delete entire row if contains certain text in Excel

3) Converting numbers stored as text to numbers via macro in Excel

4) Can I apply formatting to a number concatenated with a Text?

5) Concatenate number with text but keeping in number format in Excel

6) How can I get row count of filtered data?

7) How can I have text autocomplete based on values previously entered in Column?

8) How can I have text autocomplete by typing in a short code for the text?

9) Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

10) How can I loop through all WorkSheets and get their names using VBA?

11) Is there a way to get a log value of a number using Excel?

12) How can I get the information about my current operating environment (OS version, Excel version, Current Directory)

13) How do I change the rating numbers to rating labels?

14) I track a stock on a daily basis and enter the Open, High, Low and Close values for every trading day. In Excel, how can I automatically get High and Low values for the last 10 trading days?

15) I conducted a test and the test scores are available - how can I get Percentile rank of students?