+ Reply to Thread
Results 1 to 9 of 9

How to extract the most frequent pattern formed out of six numbers

  1. #1
    Registered User
    Join Date
    03-31-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    4

    Smile How to extract the most frequent pattern formed out of six numbers

    Hello, after searching on this forum and on youtube I considered that I should post my question on how to extract the most frequent pattern formed out of 6 numbers out of a total of 1192 patterns.

    For example, these are my first four patterns:

    1 0 0 0 1 0
    1 0 1 0 1 1
    1 1 0 0 1 0
    1 1 1 0 1 1

    and let's say that the next pattern would have been:

    1 1 0 0 1 0

    For example, I would like a result that would give me: "1 1 0 0 1 0'' is the most frequent pattern. (not this pattern!)

    I found on this forum this formula:

    "With
    A1:A15 containing your posted data
    and
    B1:B4 containing your posted sequence to be found

    This formula returns the count of that sequence in A1:A15

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    "

    But I do not want a certain sequence to be found, this is my problem.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to extract the most frequent pattern formed out of six numbers

    ..For example, I would like a result that would give me: "1 1 0 0 1 0'' is the most frequent pattern. (not this pattern!)
    I comfused!

    Pls upload a small sample workbook, typing manually your expected result, explaining why, this will be the correct result.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    03-31-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to extract the most frequent pattern formed out of six numbers

    First of all, thank you for answering.

    In this example there are there are 64 patterns, each pattern combined out of 6 numbers.
    I would like to find out what pattern is the most frequent out of these patterns.
    I do not want to find out if a certain pattern is the most frequent but what pattern is the most frequent.
    Attached Files Attached Files

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to extract the most frequent pattern formed out of six numbers

    OK. In your example, these 2 patterns appear 4 times eatch one of these.

    1 0 0 0 1 0

    1 1 1 0 1 0

    Do you want both of these as correct result?

  5. #5
    Registered User
    Join Date
    03-31-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to extract the most frequent pattern formed out of six numbers

    If these two patterns occur the most often, then these two patterns must appear as the correct result.

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to extract the most frequent pattern formed out of six numbers

    TRy

    In G2 and copy down.

    =SUMPRODUCT((A2:$A$65=A2)*(B2:$B$65=B2)*(C2:$C$65=C2)*(D2:$D$65=D2)*(E2:$E$65=E2)*(F2:$F$65=F2))

    In G1=MAX(C2:G65)

    G will be a hidden and helper column.

    Then use this ARRAY formula. Copy down and across.

    =IFERROR(INDEX(A$2:A$65;SMALL(IF($G$2:$G$65=$G$1;ROW(A$2:A$65)-1);ROW(A1)));"")
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-31-2013
    Location
    Bucharest
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How to extract the most frequent pattern formed out of six numbers

    Thank you!

    I added a star to your reputation.

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to extract the most frequent pattern formed out of six numbers

    ............................

  9. #9
    Registered User
    Join Date
    10-03-2014
    Location
    Bandung
    MS-Off Ver
    Ms. Office 2007
    Posts
    1

    Re: How to extract the most frequent pattern formed out of six numbers

    Nice Info
    I have a Homework about frequent pattern too
    so, it help me full

+ 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