Finding top 5

  • Thread starter Thread starter Brad
  • Start date Start date
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?
 
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
 
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.
 
You must log in or register to reply here.

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

Similar Threads

Excel crashes and restarts when accessing newly created worksheet 2
Best games of the last decade 5
Weighted randbetween text list 3
Find the correct sheet then find a value on that sheet 8
How to hide password? 1
Add to a "number as text" 10
Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list onsame & another sheet 27
Find string from column A in Range("B2:F7") list the header of that column/s 5

Back
Top