Return most frequently occurring text

To return the most frequently occurring text you can use a combination of the Excel INDEX, MODE, MATCH and IF functions that can be applied into Excel or VBA

Example: Return most frequently occurring text

METHOD 1. Return most frequently occurring text

EXCEL

=INDEX(B5:B9,MODE(MATCH(B5:B9,B5:B9,0)))

The formula uses a combination of the Excel INDEX, MODE and MATCH functions to return the most frequently occurring text in a specified range.
Given the Excel MATCH function returns the position of the first match, by applying the same range to the lookup value and the lookup array the MATCH function will return a range of results (numbers) where each number represents the first occurring position of the value.

The formula then applies the Excel MODE function to return the most frequently occurring number from the range of numbers calculated through the use of the Excel MATCH function. In this example the most occurring number will be (1) associated with Bread.

The final step is to return the text associated with the most frequently occurring number. This is achieved through the use of the Excel INDEX function. Using the same range as was applied in the Excel MODE and MATCH functions (B5:B9) it would return the first value in the range, which in this example is Bread.

Please note that this formula will return an #NA error if at least one of the cells in the selected range is blank. To account for this you need to apply an array formula, shown in the Excel Method 2.

METHOD 2. Return most frequently occurring text if range contains blank cells

EXCEL

{=INDEX(B5:B9,MODE(IF(B5:B9<>"",(MATCH(B5:B9,B5:B9,0)))))}

This is an array formula that can be used if the range that you are selecting includes blank cells. This formula uses the Excel INDEX, IF, MODE and MATCH functions to calculate the most frequently occurring text. The principles of this formula is basically the same as the formula in the Excel Method 1. The difference between the two formulas is that this formula uses the IF statement to test for blank cells.

Please note that this is an array formula, therefore once you have entered the formula you must press Ctrl + Shift + Enter keys simultaneously.

METHOD 1. Return most frequently occurring text using VBA

VBA

Sub Return_most _requently_occurring_text()

'declare variables
Dim rng As Range
Dim selectrng As Range
Dim ws As Worksheet

Set ws = Worksheets("Analysis")
Set selectrng = Range("B5:B9")

'return most frequently occurring text from a range
With CreateObject("scripting.dictionary")

For Each rng In selectrng

If rng.Value <> "" Then
.Item(rng.Value) = .Item(rng.Value) + 1

If .Item(rng.Value) > occur Then
occur = .Item(rng.Value)
freqtext = rng.Value
End If

End If

ws.Range("E4") = freqtext
End With

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are assessing range ("B5:B9") for the most frequently occurring text.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range that you want to assess for the most frequently occurring text by changing the range ("B5:B9") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.

ADDITIONAL NOTES
Note 1: This VBA code accounts for blank cells in the specified range. Therefore, the VBA code will still return the most frequently occurring text even if there are blank cells in the selected range.

METHOD 1. Create a function to return the most frequently occurring text

CREATE FUNCTION

Function FREQOCCURRINGTEXT(select_rng As Range)

'create a new function to return most frequently occurring text in a range

With CreateObject("scripting.dictionary")

For Each rng In select_rng

If rng.Value <> "" Then
.Item(rng.Value) = .Item(rng.Value) + 1

If .Item(rng.Value) > occur Then
occur = .Item(rng.Value)
FREQOCCURRINGTEXT = rng.Value
End If

End If

End With

End Function

ADDITIONAL NOTES
Note 1: The FREQOCCURRINGTEXT function will return the most frequently occurring text in the selected range.
Note 2: To apply this function you will need to copy and paste this VBA code into the Excel workbook. This will create the FREQOCCURRINGTEXT function which only has one parameter, being the range from which you want to return the most frequently occurring text.

Explanation about the formulas used to return most frequently occurring text

EXPLANATION

EXPLANATION
This tutorial shows and explains how to return the most frequently occurring text in a range, by using Excel formulas and VBA.

Excel Methods: This tutorial provides two Excel methods that can be applied to return the most frequently occurring text in a range.

The first method uses a combination of the Excel INDEX, MODE and MATCH functions to return the most frequently occurring text in a range. The formula applied for this method will return an #NA error if at least one of the cells in the range is blank.

The second method uses a combination of the Excel INDEX, IF, MODE and MATCH functions to return the most frequently occurring text in a range. This method is principally the same as the first method, however, the formula applied in this method will account for blank cells. Therefore, if the selected range have a blank cell the formula will still return the most frequently occurring text. The formula in this method is an array formula, therefore when you have entered the formula you must press Ctrl + Shift + Enter keys simultaneously.

VBA Method: This tutorial provides one VBA method that can be applied to most frequently occurring text in a range.

Create Function: This tutorial shows how to create a new function that will return the most frequently occurring text. The function that we create in this example is called FREQOCCURRINGTEXT. To do this you will need to create the VBA code first, as per our example, and then you can apply this function into a cell and select the range from which you want to return the most frequently occurring text.

FORMULAS
=INDEX(range,MODE(MATCH(range,range,0)))

FORMULAS (array)
{=INDEX(range,MODE(IF(range<>"",(MATCH(range,range,0)))))}

FORMULAS (using FREQOCCURRINGTEXT function)
=FREQOCCURRINGTEXT(range)

ARGUMENTS
range: The range from which to return the most frequently occurring text.

Related Functions	Description	Related Functons and Description
INDEX Function	The Excel INDEX function returns a value that is referenced from a specified range
MATCH Function	The Excel MATCH function searches for a specified value in a range and returns its position relative to the range
IF Function	The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE