Excel How to find the most common pairs and triplets numbers?

U

ubatu05

Joined
Nov 23, 2018
Messages
4
Reaction score
1
I have over 3000 rows of data composed of numbers from 1 to 90 on columns A
to E.

I need to find out the most pair / triplet for the all table. Perhaps
the following example will explain better

A B C D E
32 37 48 80 85
22 37 51 80 84
9 14 18 63 71
1 22 27 59 61

Most common pair = 37 80
Most common triplet =0

Maybe the results could go in a sheet named "Results" and :-
(1) The Pairs go in Cells "A1" & "B1" going down and the total times
appeared in Cell "C1" going down.
(2) The Triples go in Cells "E1", "F1" & "G1" going down and the total
times appeared in Cell "H1" going down.

Thanks in advance
 
Welcome to the forum @ubatu05! Looks like you accidentally posted your query in the archive but I've moved it into the main forum now so that people can see it :)
 
Do you know how to program in Visual Basic for Applications? That is the language used to customize workbooks in Office and Excel. What you want to do is go through each Row and then Compare each Column to the next one and if you find a match then Add a Row to the ReportSheet.

This is a link to the Excel VBA reference: https://msdn.microsoft.com/en-us/vba/vba-excel

You might be able to do this with an Excel Function also but I'm not sure what the name of it is :fool:
https://support.office.com/en-us/excel
https://support.office.com/en-us/ar...pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
 
Hello Abraham,
I am not good with vba. Just need assistance as to how to go about the problem stated above. Humbly, i ask anyone with a solution to assist please.
 
Sure thing, I'm happy to help but my knowledge is limited.

To help me understand the situation, what makes those numbers the most common pair? From the example there seem to be two pairs, 37 and 80, so there's a pair of pairs...? Just to clarify my understanding, a 'pair' is two identical numbers which are next to each other vertically, is that right? Would the same be true for a triplet, except three numbers not two?
 
Back
Top