JM
heres a vba solution. put your numbers to be checked in row 1 col's EFG all boxed or str comb will be displayed startin in row 4 then exact order will be displayed starting in col E
I always recommend you copy your data to a new name, then close the original and work with the copy. if you don't like the results just delete and all is well.
I apologise if you already know all of this, but untill recently I could not comprehend these first few steps and wasted years continuing to work in DOS and GWBasic
with spreadsheet open press alt F8. type the name you want to use for this program in line below macro name. lets assume test then click on create. you will now have a window with the following lines
Sub test()
End Sub
your code goes between these lines. copy the code below and paste into this space in your spreadsheet. click on the red x and your back to your spreadsheet.
to run the file press alt F8 then select the file name if not already selected then click run and program executes. if you want to change some part of the program press alt F11 and you can see the code. as long as you are in your copy, change any thing you want to see what effect it has. always save you data.
good luck
p8
' Sub prev_box_hits() name i used
' target numbers in col a assumed compressed as 123 not 1 2 3
' steps
' dim some variables
' set up loops
'decompress #'s
' store target number in array
' place next # to be tested in a different array
' compare the two arrays if all #'s match then it is a box match
' subtract target # location (row #) from # to be tested row # put difference in col c or your choice
'
mt = 4 'start matches at row 4
mx = 4000 ' change to the # of records your database has+a few hundred
Dim tn(9), tar(9), dn(4000, 3) ' change to match variable mx
' tn=test # tar=target # dn decompressed #'s mx sets of 3 #'s
GoSub decompress 'convert to single # and store in memory for fast access
' all the digits are now in dn(x,y)
'For x = 1 To mx: For y = 1 To 3: Cells(x, y + 4) = dn(x, y): Next y: Next x
'use above line if you want to verify #'s were converted corectly
'numbers to be tested in row 1 col 5,6,7or E,F,G
For Z = 0 To 9: tar(Z) = 0: Next Z 'zero out array
For y = 5 To 7: a = Cells(1, y)
tar(a) = tar(a) + 1
Next y ' you have loaded array tar() with a count for each target #
For y = 0 To 9: Cells(3, y + 2) = tar(y): Next y
For w = 1 To mx 'loop thru remaining data look for match
For Z = 0 To 9: tn(Z) = 0: Next Z ' zero this array before every test
For y = 1 To 3: a = dn(w, y)
tn(a) = tn(a) + 1
Next y ' you have loaded array tn() with a count for each target #
GoSub compare ' you could do this in line i think its easier to follow this way
Next w ' complete this loop
Exit Sub 'exit this program all below is called from above
decompress:
For x = 1 To mx
Z = Cells(x, 1) ' x is row, 1 is col change if data in different col
a = Mid(Z, 1, 1) 'first character
b = Mid(Z, 2, 1) ' normally i would put in a loop this is easier to understand
c = Mid(Z, 3, 1)
If a = "" Then mx = x - 1: GoTo done 'out of data adjust mx value then exit
dn(x, 1) = a: dn(x, 2) = b: dn(x, 3) = c
Next x
done:
Return
compare: ' compare the two arrays
m = 0
For Z = 0 To 9
If tn(Z) = tar(Z) Then m = m + 1
Next Z
If m = 10 Then
Cells(mt, 3) = w ' box or str match in previous game # display in col C
For y = 1 To 3: Cells(mt, y + 4) = dn(w, y)
Next y
mt = mt + 1
End If
Return
'End Sub only want one