Can Excel look thru column of text & derive most commone
 

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?

Can Excel look thru column of text & derive most commone
 

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


"camsown" wrote:

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?

Can Excel look thru column of text & derive most commone
 

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

"camsown" wrote in message
...
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?

Can Excel look thru column of text & derive most commone
 

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.

--
Biff
Microsoft Excel MVP


"camsown" wrote in message
...
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?

Can Excel look thru column of text & derive most commone
 

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

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

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


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

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


"camsown" wrote:

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?