Can Excel look thru column of text & derive most commone

C

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?
 
J

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
 
D

David Biddulph

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

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.
 
R

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---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top