Identify recurring subsets of numbers within multiple lottery draws

Please Login or Register  to view this content.

The above is an example of a table of previously drawn lottery numbers and I am trying to work out what formula to use that would show how many times 2 or more numbers have been drawn together but can't seem to get it to look across multiple columns... for example 17 & 28 have been drawn together but are in different columns when they have been drawn....is there a formula for this or am i just grasping at nothing here... fairly new to excel so treat me like a noob when explaining.

Any help greatly appreciated

Regards

Renoldy891

Hmm.
I've solved this like:
Let say that data are in A:E. So, in column F I concatenated all data with comma (like 26,29,37,39,44 for 1st row). Does not matter that CONCATENATE/TEXTJOIN or simple =A1&","&.....
Then numbers to find (let say H1 & I1) I've also concatenated BUT with * (wildcard) as separator (like *17*28*) BE SURE THAT STAR is on start and end.
and now is easy:
into cell G1:

Please Login or Register  to view this content.

$K$1 is cell with wildcarded string numbers to find.

Hello Renoldy891. Welcome to the forum.

In that attached please find this formula in G2:K6

Formula:

Please Login or Register  to view this content.

Originally Posted by Renoldy891

... it only seems to pull info from the immediate line above. What I am trying to achieve is something that would check the whole sheet of approx 1000+ lines, for example if 17 and 28 came out together further down the sheet, it would identify it as a repeated combination

The distance (rows down) shouldn't affect the output. Try forcing some numbers to replicate that and let me know.

scartch that hopefully this sends

So it does.

Hmm. That is unexpected.

I have another solution but is won't handle more than about 20 rows ... and it is a beast.

I'll have to think on this one a bit.

Been working on this one. It is challenging.

I have about 3 variations on one approach that treats 2 matching/3 matching/4 .... etc separately and you will likely want separate sheets (workbooks?) for each. They are all array formulas. Using these on 1,000 rows (20-30 columns of output) will take a l-o-n-g time to calculate if Excel can handle it at all. I have not found a work around for that. It will also create file bloat.

I have another that returns all matches greater than 1, but it doesn't organize in an informative way. i.e. they are not grouped according to 2 match, 3 match etc. So far I have not found a work around for that either.

Please let us know which output you favor.

RE: the performance hits and file sizes. For those reasons I don't recommend any of these. If some one else doesn't come up with acceptable formula solution you might want to consider asking a Moderator or Administrator to move this thread to the VBA or Pivot table(?) sub-forum. VBA/Pivot may have tools to handle the logic more efficiently. Please do not do this yourself. That's called double posting.

Please see the attached. There are 5 sheets. 'MATCH-MODE.MULT', 'COUNTIF-MODE.MULT', 'MATCH-MATCH - 2', 'MATCH-MATCH - 3)' and 'MATCH > 1'.
The first four have 2 helper rows and a helper cell. The fifth one does not. The MATCH-MATCH ones perform noticeably faster.

The formulas (array entered) in Sheet order are

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Will do, and you are welcome.

I will move your thread to the VBA forum.

If you later determine that is not the appropriate forum let me know. We might try moving to the Charting & Pivots forum. They do some amazing things there as well.

This is a challenging project. So in the meantime I may continue working on another formula solution.