Formula to count draws ago a pair of numbers were drawn together

roberto

Member
Hello good people. Help.
Please I need a formula to return how many draws ago a pair of selected numbers appeared together. So for example with the sample data below if I needed to find out how many draws ago the numbers “6” & “30” were drawn together it should return “5”. Likewise Numbers “22” & “44” should return “11” in N4. P.S. Latest draws are at the top.

https://www.mediafire.com/file/9x0aq0189lpc0l3/LastAppear2.JPG/file
 

Frank

Member
I cant give you a single formula that will do this, as there is more than one thing you need to do to find and count the "draws ago" of the pair in your results list. First the pair have to be identified where they occur together in your table of results and marked. Secondly having found this, the row where the last occurrence of the pair happens needs to be extracted and shown.
It is easier and more useful, in my opinion to first mark ( in another column) where the pairs are found and identify the row with the word "FOUND".
The formula to do this is :- In cell K2 enter the formula :- =IF(AND(IF(ISNA(MATCH($N$2,D2:J2,0)),0,1),IF(ISNA(MATCH($N$3,D2:J2,0)),0,1)),"FOUND",""). Copy this formula down column K as far as your results list goes.

Then to show where the last ocurrence was (the draws ago) you need to count how far down the list the first occurence of the word "FOUND" appears. This formula entered in cell N4 :- =INDEX(MATCH("FOUND",K2:K10000,0),0) will do that job.
Note that your counter numbers in columns A and B are not being used by any formula. The above formula just counts how far down your table the first pair found is.

Because you have a marker on the correct row, you could if you wished use conditional formatting to highlight the two numbers concerned. In Microsoft 365 this is how I do it ( I must admit its a bit unforgiving even for the experienced)
Click on any cell in the results
Home>conditional formatting> manage rules> new rule> use a formula to determine which
cells.

in the box "Format values where this formula is true":-

paste in this formula :- =AND(OR(D2=$N$2,D2=$N$3),$K2="FOUND")

click format, set the fill colour , click Ok.

In the next dialog click Ok then in the "Applies to" box, delete any contents and replace with =$D$2:$J$2000 ( the 2000 is just to cover all your possible rows, could be more or less)

Click Apply
Click OK to finish.
Fingers crossed that works ...🙏

Please let me know how you get on. 🙂
 

roberto

Member
Waow Frank. Most grateful for your detailed response. Please let me explore your suggestions and update you. Gracias
 
Last edited:

roberto

Member
That works perfectly, Frank. Many Thanks. Was just wondering if it is possible to return just the Row number of the first hit using either the "SMALL" or "MIN" functions hence bypassing the helper column "K".
 

Frank

Member
Hi Roberto,

The problem with the SMALL or MIN functions is that they work on ranges or lists of numbers specified in the formula. Initially, we dont know any figures to check against, so they are unuseable. The problem we are up against is that you have to ask the question"does this pair match any numbers in this result", not just once but multiple times, as many times as there are results. Which means there are multiple answers of a logical type e.g TRUE or FALSE, again as many times as there are results. These answers have to be stored somewhere in order to then ask a second question, "where is the first occurence of a TRUE result in this list ?". Column K replaces TRUE with "FOUND" where applicable, so there is now a range available to count in.
You could replace FOUND with a full stop, and then search for the first full stop by amending the code, It would work and the full stop would be barely noticeable. Alternatively you could just hide column K.
Or you could first make sure all the formulas in column K go down as far as reasonably sensible, then select the grey column label K, right click and select "cut," scroll to the right to a far away column you will never use, like AAA, click on the grey column header, right click select "Paste". There - it is gone forever, you can forget it and never know its there. Everything still works.
I hope this helps.
 

roberto

Member
Very well explained, Frank. From the options you suggest I’m inclined to go down “hiding the K column” route as I need to capture the resulting data in one cell with no visible trace dependants.
 

Sidebar

Top