+ Reply to Thread
Results 1 to 5 of 5

Formula to Display Top 10 Most Frequent Results - Skipping Gaps and Duplicates - Text

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Lightbulb Formula to Display Top 10 Most Frequent Results - Skipping Gaps and Duplicates - Text

    Example File:
    Monthly Stats Example.xlsx

    Hi,

    Long time lurker, first time poster. I have been asked to produce a stat sheet for my company and have come across an issue trying to generate the top 10 most frequent results in a list.

    I am looking to do this as a function rather than a pivot table as this is the way I have set the rest of the data to update.

    I have searched around and seen some various examples, some that kinda work, some I can't get working and some I'm not even sure how to implement!

    The closest I have got is with using the following code:

    Please Login or Register  to view this content.
    The data is found in column F and has a series of repeating data which I need to find the 10 most frequent. For client confidentiality I have had to remove this data and replace it with data from another column for the attached example.

    With the code used above I am getting duplicates, such as:

    Question Response
    Question Response
    Question Response
    Client Requests
    Question Response
    Equipment Sent
    Question Response
    Configuration
    Equipment Sent
    Customer Error

    I also need the code to skip any missing information, as this is possible to occur and the removal of just one cell will result in the code erroring.

    I have attached a broken down example of how far I have got.

    I am not looking for someone to do the work for me, but am simply lost and stuck and need pointing in the direction and I have always found responses on this site very useful.

    Thank you in advance for any help given.

    Regards, James

    Example File:
    Monthly Stats Example.xlsx

  2. #2
    Registered User
    Join Date
    09-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula to Display Top 10 Most Frequent Results - Skipping Gaps and Duplicates - Text

    Just a quick bump

  3. #3
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,456

    Re: Formula to Display Top 10 Most Frequent Results - Skipping Gaps and Duplicates - Text

    Forum rules suggest waiting about 24 hrs before bumping - Thx
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  4. #4
    Registered User
    Join Date
    09-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula to Display Top 10 Most Frequent Results - Skipping Gaps and Duplicates - Text

    Sorry, will keep that in mind

  5. #5
    Registered User
    Join Date
    09-25-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula to Display Top 10 Most Frequent Results - Skipping Gaps and Duplicates - Text

    So I have got a little bit further, using the following code I have managed to generate the second most popular and below allowing for gaps in my data.

    Please Login or Register  to view this content.
    However to extract the most popular result I am using:

    Please Login or Register  to view this content.
    This code errors when there are blanks in it's results (hence the $F$648 rather than $F$5000)
    I am trying to add the if statement from the first code but this either errors or gives me the results for the second most popular result.

    Am I being really stupid here and missing something obvious?
    Last edited by jamezp1; 05-16-2013 at 06:43 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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