+ Reply to Thread
Results 1 to 4 of 4

Counting frequency and ranking

  1. #1
    Registered User
    Join Date
    05-22-2005
    Posts
    38

    Question Counting frequency and ranking

    Morning All,

    I have a large list of numbers running down Colum F (approx 30,000 numbers) and I need to see how many times each number thats in the list is repeated.

    After I know this I then need to rank the list according to the number of occurrences.

    Can anyone help.

    Thanks

    James

  2. #2
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Counting frequency and ranking

    The best approach is Pivot Table but if you don't have knowledge about it then you can make make this list by doing below steps.

    1) Select the entire column F
    2) Open the Data...Filter...Advanced Filter menu item
    3) Check the boxes for "Unique records only" and "Filter the list, in place"
    4) Click OK. Excel will now collapse the list into just the unique records. All other rows are hidden.
    5) Enter this formula in cell G2: =COUNTIF($F$2:$F$30000,F2)
    6) Copy this formula down to the end of your list

    Now you can see each item occurrence.
    Last edited by mubashir aziz; 03-24-2009 at 07:30 AM.

  3. #3
    Registered User
    Join Date
    05-22-2005
    Posts
    38

    Re: Counting frequency and ranking

    thanks for your help.

  4. #4
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Counting frequency and ranking

    If still you can make a separte list then please do let me know .

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1