+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Find which group of number repeats the most

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    Idaho
    MS-Off Ver
    Excel 2007
    Posts
    1

    Find which group of number repeats the most

    Hi all,
    I've been trying to figure this out for a while and can't find anything online to help so thought I'd post it here.

    I have a list of numbers with three columns.
    Each column has a number in it.

    What I need to find is what combination of numbers occurs the most across the whole row.
    The kicker is that I don't care about the order of the numbers, for instance:
    1 2 3
    3 2 1
    2 1 3
    Etc..

    Are all the same, as far as this goes, and should be identified as a duplicate "pattern
    Please Login or Register  to view this content.
    In that example.. rows 1, 3 and 5 match the criteria.. so my most frequently repeating pattern consists of the numbers 1, 2 and 3.

    I hope this makes sense and thanks for any assistance!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Find which group of number repeats the most

    Hi Showson,

    Welcome to the forum.

    Just as an option, have 4th column as well and where add the entries on left:-

    column 4
    6
    12
    6
    13
    6


    Now you can easily see that 6 is appearing most hence rows where 6 is there as total is actually having most frequently repeating pattern. Cheers

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Find which group of number repeats the most

    Try this user defined function.

    Please Login or Register  to view this content.
    Paste this into a new module in the VBA editor (Alt F11) and use the formula like =GetMaxSortedCombination(A1:C27) in your sheet.
    Martin

+ 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