+ Reply to Thread
Results 1 to 15 of 15

Request help listing top occurring values in list -index/mode/match array

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel 2003
    Posts
    7

    Request help listing top occurring values in list -index/mode/match array

    I have a multiple columns of both text, numbers, etc. I'm attempting to list the top-5 occurring values, and in some places the top 10 (just top whatever, really).
    This will be used between Excel 2010 and Excel 2003
    The number of rows in the columns being indexed number between 30,000 and 60,000


    I'm using this array to get the top occurring text or number in a column, but do not know how to create the 2nd most occurring, 3rd most, etc.

    =INDEX(r:r,MODE(IF(r:r<>0,MATCH(r:r,r:r,0)))) This is entered as an array (ctr shft enter)


    Is there a modification to this formula/array, or a better one that can be used to calculate the top occurring fields? I have searched the forums and found similar situations, but none with the solution that will output this

    Thank you for your time.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Request help listing top occurring values in list -index/mode/match array

    There may be an easier, single-formula approach, but this is what I came up with:

    With
    R5:R10000 containin alpha-numeric entries, blanks are allowed.

    This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) begins the list of the most occurring entries
    Please Login or Register  to view this content.
    Copy that formula down as far as you need.

    Note_1: That formula will stop listing when it reaches the count of unique values in $R$5:$R$10000
    Note_2: Some of those addresses reference through cell R10001

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Request help listing top occurring values in list -index/mode/match array

    Wow, were you a mathematician in an n-1 life?

    That certainly gets the job done, and takes less time than my current method of creating a bunch of macros/helper rows, etc. Cut down my calc time by 80% I'd say!

    Thanks!!!

    Does anyone else have any other ideas that would be recommended?

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Request help listing top occurring values in list -index/mode/match array

    Quote Originally Posted by jerrygoldsmith View Post
    Wow, were you a mathematician in an n-1 life?

    That certainly gets the job done, and takes less time than my current method of creating a bunch of macros/helper rows, etc. Cut down my calc time by 80% I'd say!

    Thanks!!!

    Does anyone else have any other ideas that would be recommended?
    In order :
    1) Maybe?
    2) Wow! 80% is good, right?
    3) You're very welcome.
    4) I hope so...that formula is a beast!

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Request help listing top occurring values in list -index/mode/match array

    maybe with helpers? q5 =if(countif($r$5:r5,r5)=countif($r$5:$r$60000,r5),countif($r$5:$r$60000,r5),"") then unique rank then lookup small1.2.3. and so on?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    09-25-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Request help listing top occurring values in list -index/mode/match array

    I applied the formula to my top-20 lists (was just my top 5's) and now it's snailing. 64bit office version (2010) 12gb ram, i7 2.5ghz (w hyperthreading) and it's hitting 100% for 10 minutes while calculating. Granted, I'm telling it to run through 65,000 cells... Still quicker than the 20+ minutes it took before.

    Unless someone has a quicker solution, I'll say solved and keep using it until something else comes along. Thanks again, and yes it is a beast!

    *edit

    Sorry Martindwilson, didn't see your post until after I posted this. My use of helper columns before bogged things down pretty bad, but I'll give yours a shot as it's different than what I used. Thanks for the info!
    Last edited by jerrygoldsmith; 11-28-2012 at 06:06 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Request help listing top occurring values in list -index/mode/match array

    For this sort of numbers, you could consider a macro, see attachment for example.
    Obviously you'll need to adapt the ranges.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-25-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Request help listing top occurring values in list -index/mode/match array

    Thanks!!! That should fit the bill and be so much quicker!! I've modified the macro to fit my areas of the sheet. It does not sort by number of occurrences, and I made the 'e2' a large number (5,000) to account for a large list of unique values so I can get counted results higher than '99'. Problem is it now gives an error after it successfully runs
    "Run-time Error '-2146233086 (8013152)':
    Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index

    I can add commands to make it sort (starts at 99, then down to 1, the 300's for example list in the middle of the results), but I'm more interested in the error.

    Do you have any suggestions? And thanks again, this seems to be perfect so far!!!

  9. #9
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Request help listing top occurring values in list -index/mode/match array

    It does not sort by number of occurrences
    In the sample workbook from post 7 ,it clearly sorts by number of occurrences, see JPG
    I made the 'e2' a large number (5,000)
    The error seems to indicate that the number in E2 is too big, you may be asking for the 5000th largest of only 4000 unique values?
    Attached Images Attached Images
    Last edited by WHER; 11-29-2012 at 12:44 PM.

  10. #10
    Registered User
    Join Date
    09-25-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Request help listing top occurring values in list -index/mode/match array

    Yes, yours sorts perfectly by occurrences. Mine isn't, possibly due to the large number of data, but I've got some 'large' and index formulas to return that information in order.

    I've also changed that 'e2' to a formula which counts the total number of unique values total in the whole range. So now the error goes away, it adjusts dynamically to new batches of data, and I can return multiple list requirements (top 5, 10, 10, etc).

    Thank you so much! Image attached showing sorting, and results from the additional formula addedNew Picture.jpg

  11. #11
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Request help listing top occurring values in list -index/mode/match array

    From your JPG i see (and understand) the sorting problem, thanks for pointing this out.

  12. #12
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Request help listing top occurring values in list -index/mode/match array

    From your JPG i see (and understand) the sorting problem, thanks for pointing this out.
    Adapt code in 2 places:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    The "Cells(i + 1 .. etc" needs adapting to your layout like before.

  13. #13
    Registered User
    Join Date
    09-25-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Request help listing top occurring values in list -index/mode/match array

    Magical!

    Its working so far, I'm going to make a few more implementations in my sheet and see if any other errors pop up. Thank you so much again!
    Obviously I need to get back onto the VB bandwagon, if I'm going to keep doing these things.....

  14. #14
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Request help listing top occurring values in list -index/mode/match array

    I'm surprised I didn't think of this sooner, but...
    A Pivot Table easily lists the Top-N values in descending order by count!

  15. #15
    Registered User
    Join Date
    09-25-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Request help listing top occurring values in list -index/mode/match array

    Thank you all for your help! I greatly appreciate it!

    Problem solved!

+ 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