Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.

Nou pe simpatie:
Ank_beleaua

Femeie
25 ani
Bucuresti
cauta Barbat
35 - 51 ani

Mrrrr's Forum (VIEW ONLY)Reguli Inregistrare Login PozeNu sunteti logat. Lista Forumurilor Pe Tematici

Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides /

[EXCEL] Most Frequently Occurring Word [INDEX,MODE,MATCH,ARRAY,VBA]

Pagini: 1

Moderat de TRaP, TonyTzu

Mrrrr
AdMiN
Postari: 2388

Source:
http://www.excel-easy.com/examples/most ... -word.html

Assuming that for the interval E2:E55 you want to know which is the most frequent word, in E1 you can write the following formula:
=INDEX(E2:E55;MODE(MATCH(E2:E55;E2:E55;0)))

If you apply just the following formula, you find out the position of the most frequent word in the range you set:
=MODE(MATCH(E2:E55;E2:E55;0))

The INDEX in the first formula only shows the word that is at that position.

The link above says these formulas are array formulas so you enter them with CTRL+SHIFT+ENTER.
For me they worked also without that key combination, just plain ENTER.

More sources:
https://www.extendoffice.com/documents/ ... value.html
https://exceljet.net/formula/most-frequ ... rring-text

_______________________________________

Mrrrr
AdMiN
Postari: 2388

If there are empty cells in the range, you use the following ARRAY formula:
=INDEX(B5:F5;MODE(IF(B5:F5<>"";MATCH(B5:F5;B5:F5;0))))

If you want to use multiple rows / columns, you must use VBA code. See my post below.

_______________________________________

Mrrrr
AdMiN
Postari: 2388

If the above won't work, like they haven't worked for me in one case with multiple rows and columns, I used VBA:

Sub FindFrequency()

Dim Rng As Range
Dim WorkRng As Range
Set dic = CreateObject("scripting.dictionary")
On Error Resume Next

xTitleId = "Select range to count the most frequent word"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xMax = 0
xOutValue = ""

For Each Rng In WorkRng
xValue = Rng.Value
If xValue <> "" Then
dic(xValue) = dic(xValue) + 1
xCount = dic(xValue)
If xCount > xMax Then
xMax = xCount
xOutValue = xValue
End If
End If
Next

MsgBox "The most common value is: " & xOutValue & ". It appeared " & xMax & " Times"
End Sub

Source:
https://www.extendoffice.com/documents/ ... value.html

_______________________________________

Pagini: 1

Mergi la