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

Originally Posted by Glenn Kennedy

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.

I expect you will need a VBA solution.

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

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.

.

Originally Posted by JohnTopley

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

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).

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.

Originally Posted by JohnTopley

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.

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!