Can Excel look thru column of text & derive most commone

[camsown]

I have a sheet that contains employee initials in one column. I am trying to
figure out a way for Excel to derive the most used set of initials with out
having to tell it all the possible initials. Is there a formula for this?

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=MostRepeated(A1:A8)

Function MostRepeated(varRange As Range) As String
Dim lngCount As Long, lngMost As Long
For Each cell In varRange
lngCount = WorksheetFunction.CountIf(varRange, cell)
If lngCount > lngMost Then MostRepeated = cell.Text: lngMost = lngCount
Next
End Function


If this post helps click Yes

 

[David Biddulph]

Helper column B =COUNTIF(A$1:A$100,A1) and copy down.
CF in B, =MAX(B$1:B$100)

 

[T. Valko]

Try this array formula** :

=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Assumes no empty cells within the range and there is at least one duplicate
entry. Also note, if there are equal duplicates the formula will return the
1st duplicate.

A
B
A
B
C

Both A and B have the same number of entries but A is listed 1st so the
fromula will return A.

 

[ryguy7272]

Find the most frequently occurring number:
=INDEX(A1:A367,MODE(IF(A1:A367<>"",MATCH(A1:A367,A1:A367,0))))

Find the most frequently occurring name:
=INDEX(B1:B367,MODE(IF(B1:B367<>"",MATCH(B1:B367,B1:B367,0))))

Both of these functions are committed with Ctrl+Shift+Enter, not just Enter.


HTH,
Ryan---