Finding top 5

B

Brad

I have a sheet of data (text), is there a function that will search the list
and tell me what the string is that occurs most in the list?

Can it be used to find the top 5 occurring strings?
 
R

Roger Govier

Hi Brad

With your data in column A, enter in B
=COUNTIF(A:A,A1)
Copy down as far as required.
Column B will have a count of the number of occurrences of the strings
use Autofilter to select the counts you want displayed
 
J

Joel

I would add a helper column to the worksheet that has a count of the number
of times a string occurs using countif

=countif($A$1"$A$100,A1)

A1:A100 is thhe range where all the strings are found. The strings with the
largest count occurs the most times. You can use the RANK function to get
the 5 top items.
 
Top