+ Reply to Thread
Results 1 to 13 of 13

How to count the most frequent triplets, quadruplets in given rows in Excel

  1. #1
    Registered User
    Join Date
    02-23-2021
    Location
    Washington, D.C
    MS-Off Ver
    2019
    Posts
    5

    How to count the most frequent triplets, quadruplets in given rows in Excel

    I need to find the most frequent triplets, quadruplets in a given row in Excel. There are a hundred rows and each has 8 numbers. For example, 20 out of 100 rows have these numbers 3, 5, 7, 86. Thereby, these are the most frequent quadruplets in the spreadsheet. Another example, 25 out of 100 rows contain these numbers 3, 5, 7. Thereby, these are the most frequent triplets in the spreadsheet. Note, for example, 24 out of 100 rows contain 3, 5, 8 numbers in every 24 rows but the number of frequency is less than in the previous example (25). So, we don't consider this as the most frequent triplets pattern.


    A simple example with triplets :

    3, 5, 7, 10

    3, 5, 1, 8

    7, 3, 6, 5

    5, 7, 3, 10

    Result:

    (3, 5, 7 ) = 3 times

    (3, 5, 8 ) = 1 times

    Final result:

    Triplet pattern - 3, 5, 7 as it appeared 3 times

    I need the same thing for most common quadruplets pairs in a row

    P.s. I have just a basic knowledge of Excel. Thanks.
    Attached Files Attached Files
    Last edited by Admiral000; 02-23-2021 at 06:34 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,735

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn



  3. #3
    Registered User
    Join Date
    02-23-2021
    Location
    Washington, D.C
    MS-Off Ver
    2019
    Posts
    5

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Quote Originally Posted by Glenn Kennedy View Post
    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Hi Glenn. Sure, I attached an example of 10 rows and manually counted the most frequent Quadruplets in the spreadsheet.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,664

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    I expect you will need a VBA solution.

    4 digits from 8 is 70 combinations (per row) and hundreds of rows !

  5. #5
    Registered User
    Join Date
    02-23-2021
    Location
    Washington, D.C
    MS-Off Ver
    2019
    Posts
    5

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Quote Originally Posted by JohnTopley View Post
    I expect you will need a VBA solution.

    4 digits from 8 is 70 combinations (per row) and hundreds of rows !
    Yes, good point. I didn't take the total combination into account. Is it possible to calculate in Excel if 5 numbers in each row, not 8? So, it will make 5 combinations, for each row, thus, less than in my previous example (8). If still, Excel can't handle it I can use VBA, of course, if there any code for this.
    Last edited by Admiral000; 02-23-2021 at 10:15 PM.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,474

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    With helper columns R,S,T,U.
    In L1
    Please Login or Register  to view this content.
    Without helper columns
    In L3
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,664

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Given reply in #6:

    My assumption was we had to DETERMINE (and hence COUNT) the most frequent quadruplets: the amswer in #6 ASSUMES we have found thev answer in J1 of your sample sheet. (1,5,7,11)

    If we ignore the ORDER of the digits i.e, PERMUTATION, then there are 1680 for 4 digits out of 8 (24 for each set of 4 digits x 70)

    For COMBINATIONS ( and minimum computing) each row should have data in (ascending order) so we can then match results for each row more easily,

    If this is correct, then perhaps there is a mathematical shortcut to this processing but beyond my pay scale!

    I have a program which calculates Combinations/Permutations but it would need to be modified to be tailored to what I believe is your reqirement.

    .

  8. #8
    Registered User
    Join Date
    02-23-2021
    Location
    Washington, D.C
    MS-Off Ver
    2019
    Posts
    5

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Quote Originally Posted by JohnTopley View Post
    Given reply in #6:

    My assumption was we had to DETERMINE (and hence COUNT) the most frequent quadruplets: the amswer in #6 ASSUMES we have found thev answer in J1 of your sample sheet. (1,5,7,11)
    Exactly, quadruplets need to be determined and then find/filter the most frequent ones. It's desirable to have a list of if the most frequent quadruplets in ascending order (1,5,7,11 - found 20 times; 2,5,7,9 - found 19 times and etc).

    If we ignore the ORDER of the digits i.e, PERMUTATION, then there are 1680 for 4 digits out of 8 (24 for each set of 4 digits x 70)

    For COMBINATIONS ( and minimum computing) each row should have data in (ascending order) so we can then match results for each row more easily,

    If this is correct, then perhaps there is a mathematical shortcut to this processing but beyond my pay scale!
    I'm sorry for making the example messy, I didn't take into consideration some details. I will clarify key corrections in the example:

    1. Each row will have 5 numbers, not 8 as I mistakenly wrote in the first example.
    2. Numbers in a combination can be sorted by order to reduce the total possible combinations. The main idea to find a list of numbers which is more frequently can be found in the same pattern. For example, 1234 or 2314 - doesn't matter as long as the patterns contain the same numbers. So, the combination for each row will (5!/4!*1!) = 5

    I have a program which calculates Combinations/Permutations but it would need to be modified to be tailored to what I believe is your reqirement.
    .
    It sounds interesting

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,664

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Ideally, the numbers in each row are sorted: then we can use COMBINATION ... so 5 per 4 from 5. If data is unsorted it makes comparisons more dificult as we are now into PERMUTATIONS (120 per row). the sorting makes matching much easier as we limit the number of comparisons to 5 per row but still a challenge!

    To SORT, the data will need to be transposed (rows to columns).

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,474

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Have you seen post#6

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,664

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    You answer ASSUMES the result in J1: what required is to determine from many rows what is the most frequent quadruplet i.e. find J1 in the example given.

  12. #12
    Registered User
    Join Date
    02-23-2021
    Location
    Washington, D.C
    MS-Off Ver
    2019
    Posts
    5

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    Quote Originally Posted by JohnTopley View Post
    Ideally, the numbers in each row are sorted: then we can use COMBINATION ... so 5 per 4 from 5. If data is unsorted it makes comparisons more dificult as we are now into PERMUTATIONS (120 per row). the sorting makes matching much easier as we limit the number of comparisons to 5 per row but still a challenge!

    To SORT, the data will need to be transposed (rows to columns).
    I got the data. It contains 2480 rows and 5 columns with ordered numbers in each row. I supposed it would less row but it is a lot. It can be solved in Python in a few lines of code but I didn't code for some years and totally lost skills. Anyway, will try to solve it in Python as I don't think Excel can handle so much data (I have a very basic knowledge of Excel). Thank you, Sir.

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,664

    Re: How to count the most frequent triplets, quadruplets in given rows in Excel

    I don't know Python but I have seen it used to solve number puzzles which appear in UK newspaper: it does seem ideally suited to solving this type of problem.

    Best of luck!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count matched triplets
    By Bettyoge in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-07-2020, 09:34 AM
  2. [SOLVED] How to count the most frequent number in a Column
    By Nickmsi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-17-2019, 04:40 AM
  3. expand conditional formatting from duplicates to quadruplets
    By Jamie ina jam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2016, 07:45 PM
  4. how to delete unwanted rows at frequent intervals?
    By sumesh56 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-24-2013, 04:20 AM
  5. Replies: 3
    Last Post: 03-20-2012, 09:53 PM
  6. Highlighting and Consolidating Quadruplets
    By james_evans in forum Excel General
    Replies: 4
    Last Post: 06-21-2011, 12:26 AM
  7. count most frequent value in column
    By runnerpaul in forum Excel General
    Replies: 6
    Last Post: 11-03-2006, 01:03 PM

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