As a beginner with Excel and VBA I have the following problem. I do kee
track of lottery numbers in a sheet.

Don't ask me why. It doesn't help me win a big price. There is a dra
of six numbers every week, from 1 to 45. I

keep them in six collumns. From the year 1996 to this week. I'd like t
seek combinations of numbers, say 2 or 3

numbers. If I, for example seek 3 numbers, 4-5-12 then they should sho
up highlighted or coloured or whatever,

if they excist in that combination. Or else give a statement that th
combination doesn't excist.
I have tried something with conditional formating but I can't seem t
get that to do what I want. Is there

anybody who can give me a hint!!

--
Message posted from http://www.ExcelForum.com

This should be easy, but need a bit more information as I don't know much
about lotteries.
Does the order of the numbers matter?
Does it matter in which columns the numbers are (there are 6 columns, but
you mention 3 numbers)?

RBS

"knoertje " wrote in message
...
As a beginner with Excel and VBA I have the following problem. I do keep
track of lottery numbers in a sheet.

Don't ask me why. It doesn't help me win a big price. There is a draw
of six numbers every week, from 1 to 45. I

keep them in six collumns. From the year 1996 to this week. I'd like to
seek combinations of numbers, say 2 or 3

numbers. If I, for example seek 3 numbers, 4-5-12 then they should show
up highlighted or coloured or whatever,

if they excist in that combination. Or else give a statement that the
combination doesn't excist.
I have tried something with conditional formating but I can't seem to
get that to do what I want. Is there

anybody who can give me a hint!!!


---
Message posted from http://www.ExcelForum.com/

Let's say your lottery numbers for the last 52 weeks are in A3:F54. In A1:C1
you put the three numbers you're looking for. Then select A3:F54, with A3 the
active cell. Go to Format/Conditional Formatting, select Formula Is in the 1st
dropdown, and type this for the formula

=AND(ISNUMBER(MATCH($A$1,$A3:$F3,0)),ISNUMBER(MATC H($B$1,$A3:$F3,0)),
ISNUMBER(MATCH($C$1,$A3:$F3,0)))

That all goes on one line, of course. Then select a format.

On Fri, 30 Jul 2004 16:07:00 -0500, knoertje
wrote:

As a beginner with Excel and VBA I have the following problem. I do keep
track of lottery numbers in a sheet.

Don't ask me why. It doesn't help me win a big price. There is a draw
of six numbers every week, from 1 to 45. I

keep them in six collumns. From the year 1996 to this week. I'd like to
seek combinations of numbers, say 2 or 3

numbers. If I, for example seek 3 numbers, 4-5-12 then they should show
up highlighted or coloured or whatever,

if they excist in that combination. Or else give a statement that the
combination doesn't excist.
I have tried something with conditional formating but I can't seem to
get that to do what I want. Is there

anybody who can give me a hint!!!


---
Message posted from http://www.ExcelForum.com/

I do have the numbers in column B5 to H5, which actually make it seve
numbers. The latest numbers are inserted

in Row 5 every time, because of other formulas. So the latest is alway
on top starting in B5. The order of the

numbers in the combination I'm looking for don't matter. So my meanin
is to give Excel 2 or 3 or 4 numbers and

let Excel find if that combination of 2, 3 or 4 numbers excist. If tha
combination excist I want Excel to

highlight or color the combination in the sheet or tell me that i
excist. But if that combination doesn't

excist I want it to either do nothing or give a message or so .

The formula given don't seem to work after adjusting it to my columns

--
Message posted from http://www.ExcelForum.com

OK. The best solution here would probably a VBA routine.
Will have a look at this later.

RBS

"knoertje " wrote in message
...
I do have the numbers in column B5 to H5, which actually make it seven
numbers. The latest numbers are inserted

in Row 5 every time, because of other formulas. So the latest is always
on top starting in B5. The order of the

numbers in the combination I'm looking for don't matter. So my meaning
is to give Excel 2 or 3 or 4 numbers and

let Excel find if that combination of 2, 3 or 4 numbers excist. If that
combination excist I want Excel to

highlight or color the combination in the sheet or tell me that it
excist. But if that combination doesn't

excist I want it to either do nothing or give a message or so .

The formula given don't seem to work after adjusting it to my columns.


---
Message posted from http://www.ExcelForum.com/

The best solution is probably conditional formatting.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK. The best solution here would probably a VBA routine.
Will have a look at this later.

RBS

"knoertje " wrote in message
...
I do have the numbers in column B5 to H5, which actually make it seven
numbers. The latest numbers are inserted

in Row 5 every time, because of other formulas. So the latest is always
on top starting in B5. The order of the

numbers in the combination I'm looking for don't matter. So my meaning
is to give Excel 2 or 3 or 4 numbers and

let Excel find if that combination of 2, 3 or 4 numbers excist. If that
combination excist I want Excel to

highlight or color the combination in the sheet or tell me that it
excist. But if that combination doesn't

excist I want it to either do nothing or give a message or so .

The formula given don't seem to work after adjusting it to my columns.


---
Message posted from http://www.ExcelForum.com/

Tom,

OK; I am not going to argue with that.

RBS


"Tom Ogilvy" wrote in message
...
The best solution is probably conditional formatting.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK. The best solution here would probably a VBA routine.
Will have a look at this later.

RBS

"knoertje " wrote in message
...
I do have the numbers in column B5 to H5, which actually make it seven
numbers. The latest numbers are inserted

in Row 5 every time, because of other formulas. So the latest is
always
on top starting in B5. The order of the

numbers in the combination I'm looking for don't matter. So my meaning
is to give Excel 2 or 3 or 4 numbers and

let Excel find if that combination of 2, 3 or 4 numbers excist. If
that
combination excist I want Excel to

highlight or color the combination in the sheet or tell me that it
excist. But if that combination doesn't

excist I want it to either do nothing or give a message or so .

The formula given don't seem to work after adjusting it to my columns.


---
Message posted from http://www.ExcelForum.com/

How hard can it be. Myrna already gave him the formula. If he can't adjust
the formula, then he can put his data in a new sheet to match what she
specified.

The whole thing is pretty much a waste of time anyway - the only real use
for this type of thing would be to check if he bought any winners.
--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
Tom,

OK; I am not going to argue with that.

RBS


"Tom Ogilvy" wrote in message
...
The best solution is probably conditional formatting.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK. The best solution here would probably a VBA routine.
Will have a look at this later.

RBS

"knoertje " wrote in message
...
I do have the numbers in column B5 to H5, which actually make it
seven
numbers. The latest numbers are inserted

in Row 5 every time, because of other formulas. So the latest is
always
on top starting in B5. The order of the

numbers in the combination I'm looking for don't matter. So my
meaning
is to give Excel 2 or 3 or 4 numbers and

let Excel find if that combination of 2, 3 or 4 numbers excist. If
that
combination excist I want Excel to

highlight or color the combination in the sheet or tell me that it
excist. But if that combination doesn't

excist I want it to either do nothing or give a message or so .

The formula given don't seem to work after adjusting it to my
columns.


---
Message posted from http://www.ExcelForum.com/

Tom,

I agree it is a waste of time to look for common numbers etc.
It is just that from time to time I like a little exercise in VBA, it
doesn't matter
much what the result is used for.
What I don't like about formula's in general is that they can make the
workbook much bigger and that it is often more difficult to see what is
going on (you can't comment worksheet formula's as easily as a VBA routine).

RBS


"Tom Ogilvy" wrote in message
...
How hard can it be. Myrna already gave him the formula. If he can't
adjust
the formula, then he can put his data in a new sheet to match what she
specified.

The whole thing is pretty much a waste of time anyway - the only real use
for this type of thing would be to check if he bought any winners.
--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
Tom,

OK; I am not going to argue with that.

RBS


"Tom Ogilvy" wrote in message
...
The best solution is probably conditional formatting.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK. The best solution here would probably a VBA routine.
Will have a look at this later.

RBS

"knoertje " wrote in
message
...
I do have the numbers in column B5 to H5, which actually make it
seven
numbers. The latest numbers are inserted

in Row 5 every time, because of other formulas. So the latest is
always
on top starting in B5. The order of the

numbers in the combination I'm looking for don't matter. So my
meaning
is to give Excel 2 or 3 or 4 numbers and

let Excel find if that combination of 2, 3 or 4 numbers excist. If
that
combination excist I want Excel to

highlight or color the combination in the sheet or tell me that it
excist. But if that combination doesn't

excist I want it to either do nothing or give a message or so .

The formula given don't seem to work after adjusting it to my
columns.


---
Message posted from http://www.ExcelForum.com/

I asked for some help to solve a problem. Adjusting my workbook to th
resolution given, takes a lot of work, because it is already full o
other formula's doing other things. If it to much trouble to you or
waste of time , you already wasted time in reacting. Thanks anyway,
will find another way in solving my problem

--
Message posted from http://www.ExcelForum.com

It wouldn't be a waste of time for me to write a bit of code, but I thought
it might
be a waste of time for you looking for common numbers.
If you are still interested in having a VBA solution rather than a formula
one I will have
a look and post back.

RBS

"knoertje " wrote in message
...
I asked for some help to solve a problem. Adjusting my workbook to the
resolution given, takes a lot of work, because it is already full of
other formula's doing other things. If it to much trouble to you or a
waste of time , you already wasted time in reacting. Thanks anyway, I
will find another way in solving my problem.


---
Message posted from http://www.ExcelForum.com/

Of course I'm still interested. I don't fill in any lotery forms. We al
have sense enough to know that there is no way to beat any loter
system. I do just want to keep track off the numbers. How often does
number show up, and in combination with what other number, do od
numbers show up more than even numbers, that kind of stuff. More a kin
of statistics. Some people collect stamps en som
others.............program in VBA.
I already have a few formula's that do some counting and how long ag
it was a number showed up. Now I like to see of some show up togethe
more often than others.
I go to my work every day, 5 days a week, week in and week out, mont
in, month out etc........You know what waste that is??

--
Message posted from http://www.ExcelForum.com

This simple bit of VBA will I think do what you want.
It will take the first 3 numbers of the new combination and in this examples
there are only 3 rows of old
number combinations.
You will have to work it out further to fit your requirements.
Run the Sub DoAll, which will run the other 3 Subs.


Option Explicit
Private strNew As String
Private arr3()

Sub SortLeft2Right()

Dim R As Long

For R = 5 To 8
Range(Cells(R, 2), Cells(R, 8)).Sort Key1:=Cells(R, 8), _
Order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlLeftToRight
Next

End Sub


Sub Numbers2Strings()

Dim arr1()
Dim arr2()
Dim strTemp As String
Dim i As Long
Dim c As Byte

arr1 = Range(Cells(5, 2), Cells(5, 8))

arr2 = Range(Cells(6, 2), Cells(8, 8))

ReDim arr3(1 To (8 - 5))

'put the first 3 new numbers in string
'-------------------------------------
strTemp = Chr(44)

For c = 1 To 3
strTemp = strTemp & arr1(1, c) & Chr(44)
Next

strNew = strTemp

'put the old numbers in a string array
'-------------------------------------
strTemp = Chr(44)

For i = 1 To 8 - 5
For c = 1 To 7
strTemp = strTemp & arr2(i, c) & Chr(44)
Next
arr3(i) = strTemp
strTemp = Chr(44)
Next

End Sub


Sub CompareNumbers()

Dim i As Long

For i = 1 To 8 - 5
If InStr(1, arr3(i), strNew, vbTextCompare) 0 Then
MsgBox "Bingo, we have a match in row " & i + 5, , "finding
matches"
End If
Next

End Sub


Sub DoAll()

SortLeft2Right
Numbers2Strings
CompareNumbers

End Sub



RBS


"knoertje " wrote in message
...
I asked for some help to solve a problem. Adjusting my workbook to the
resolution given, takes a lot of work, because it is already full of
other formula's doing other things. If it to much trouble to you or a
waste of time , you already wasted time in reacting. Thanks anyway, I
will find another way in solving my problem.


---
Message posted from http://www.ExcelForum.com/

Did it a bit too quick; ignore this code.
Will come back in a bit with the right code.

RBS

"RB Smissaert" wrote in message
...
This simple bit of VBA will I think do what you want.
It will take the first 3 numbers of the new combination and in this
examples
there are only 3 rows of old
number combinations.
You will have to work it out further to fit your requirements.
Run the Sub DoAll, which will run the other 3 Subs.


Option Explicit
Private strNew As String
Private arr3()

Sub SortLeft2Right()

Dim R As Long

For R = 5 To 8
Range(Cells(R, 2), Cells(R, 8)).Sort Key1:=Cells(R, 8), _
Order1:=xlAscending, _
Header:=xlNo, _
Orientation:=xlLeftToRight
Next

End Sub


Sub Numbers2Strings()

Dim arr1()
Dim arr2()
Dim strTemp As String
Dim i As Long
Dim c As Byte

arr1 = Range(Cells(5, 2), Cells(5, 8))

arr2 = Range(Cells(6, 2), Cells(8, 8))

ReDim arr3(1 To (8 - 5))

'put the first 3 new numbers in string
'-------------------------------------
strTemp = Chr(44)

For c = 1 To 3
strTemp = strTemp & arr1(1, c) & Chr(44)
Next

strNew = strTemp

'put the old numbers in a string array
'-------------------------------------
strTemp = Chr(44)

For i = 1 To 8 - 5
For c = 1 To 7
strTemp = strTemp & arr2(i, c) & Chr(44)
Next
arr3(i) = strTemp
strTemp = Chr(44)
Next

End Sub


Sub CompareNumbers()

Dim i As Long

For i = 1 To 8 - 5
If InStr(1, arr3(i), strNew, vbTextCompare) 0 Then
MsgBox "Bingo, we have a match in row " & i + 5, , "finding
matches"
End If
Next

End Sub


Sub DoAll()

SortLeft2Right
Numbers2Strings
CompareNumbers

End Sub



RBS


"knoertje " wrote in message
...
I asked for some help to solve a problem. Adjusting my workbook to the
resolution given, takes a lot of work, because it is already full of
other formula's doing other things. If it to much trouble to you or a
waste of time , you already wasted time in reacting. Thanks anyway, I
will find another way in solving my problem.


---
Message posted from http://www.ExcelForum.com/

After thinking about this properly it is in fact much simpler.
The following code will do it, same example as in my previous posting.
Again adjust to your requirements.

Option Explicit

Sub CompareNumbers()

Dim arr1()
Dim arr2()
Dim i As Long
Dim c1 As Byte
Dim c2 As Byte
Dim counter As Byte

arr1 = Range(Cells(5, 2), Cells(5, 8))
arr2 = Range(Cells(6, 2), Cells(8, 8))

For i = 1 To 8 - 5
counter = 0
For c1 = 1 To 7
For c2 = 1 To 7
If arr1(1, c1) = arr2(i, c2) Then
counter = counter + 1
Exit For
End If
Next
Next
If counter 2 Then
MsgBox "Bingo, we have a match in row " & i + 5
End If
Next

End Sub


RBS

"knoertje " wrote in message
...
Of course I'm still interested. I don't fill in any lotery forms. We all
have sense enough to know that there is no way to beat any lotery
system. I do just want to keep track off the numbers. How often does a
number show up, and in combination with what other number, do odd
numbers show up more than even numbers, that kind of stuff. More a kind
of statistics. Some people collect stamps en some
others.............program in VBA.
I already have a few formula's that do some counting and how long ago
it was a number showed up. Now I like to see of some show up together
more often than others.
I go to my work every day, 5 days a week, week in and week out, month
in, month out etc........You know what waste that is???


---
Message posted from http://www.ExcelForum.com/

If you are looking for beneficial things to do, why not adjust Myrna's
formula to your layout.

--
Regards,
Tom Ogilvy


"knoertje " wrote in message
...
I asked for some help to solve a problem. Adjusting my workbook to the
resolution given, takes a lot of work, because it is already full of
other formula's doing other things. If it to much trouble to you or a
waste of time , you already wasted time in reacting. Thanks anyway, I
will find another way in solving my problem.


---
Message posted from http://www.ExcelForum.com/

Did a bit of testing to see if it was worth it (speedwise) to check if the
new numbers couldn't make a match
anymore (3 matching numbers) and move to the next new numbers. The answer
seems to be no it isn't.
You could get the number of cycles down, but the checking for the condition
takes more time.
I did this because I am working on some 'real life' software where getting
fast through an array is important.
As I thought you might be interested in this I put all the relevant code in
here.
CompareNumbers2 is the one that will be most relevant for you.


Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private lStartTime As Long
Private lEndTime As Long

Sub CompareNumbers()

Dim arr1()
Dim arr2()
Dim LR As Long
Dim i As Long
Dim c1 As Byte
Dim c2 As Byte
Dim counter As Byte
Dim cyclecounter As Long
Dim bShortcut1 As Boolean
Dim bShortcut2 As Boolean
Dim lSleepTime As Long
Dim lWasteMax As Long

bShortcut1 = True
bShortcut2 = True

LR = 8
lSleepTime = 1000
lWasteMax = 80000000

arr1 = Range(Cells(5, 2), Cells(5, 8))
arr2 = Range(Cells(6, 2), Cells(LR, 8))

For i = 1 To LR - 5
counter = 0
'reset the format of the new numbers
'-----------------------------------
With Range(Cells(5, 2), Cells(5, 8)).Font
.Bold = False
.ColorIndex = 1
End With
For c1 = 1 To 7
For c2 = 1 To 7
'border around the new number being checked
'------------------------------------------
NoBorder Range(Cells(5, 2), Cells(5, 8))
MediumBorder Cells(5, c1 + 1)
Cells(i + 5, c2 + 1).Select
cyclecounter = cyclecounter + 1
'if match found format old and new number
'----------------------------------------
If arr1(1, c1) = arr2(i, c2) Then
With Cells(i + 5, c2 + 1).Font
.Bold = True
.ColorIndex = 3
End With
With Cells(5, c1 + 1).Font
.Bold = True
.ColorIndex = 3
End With
counter = counter + 1
'RunSleeper (lSleepTime)
RunTimeWaster (lWasteMax)
Exit For
End If
'get out if old numbers can't make it anymore
'--------------------------------------------
If bShortcut1 = True Then
If c1 - counter 5 Then
'RunSleeper (lSleepTime)
RunTimeWaster (lWasteMax)
Exit For
End If
End If
'RunSleeper (lSleepTime)
RunTimeWaster (lWasteMax)
Next
'match found, move to next new numbers
'-------------------------------------
If counter 2 Then
MsgBox "Bingo, we have a match in row " & i + 5
Exit For
End If
'get out if old numbers can't make it anymore
'--------------------------------------------
If bShortcut2 = True Then
If c1 - counter 4 Then
counter = 0
Exit For
End If
End If
Next
Next

MsgBox "finished in " & cyclecounter & " cycles"

End Sub


Sub RunSleeper(lmilliSecs As Long)

Sleep (lmilliSecs)

'otherwise the display might freeze
'this doesn't work, still can freeze
'-----------------------------------
Application.ScreenUpdating = False
Application.ScreenUpdating = True

End Sub


Sub RunTimeWaster(lMax)

Dim i As Long
Dim x As Double

For i = 1 To lMax
'do nil
Next

'otherwise the display might freeze
'----------------------------------
Application.ScreenUpdating = False
Application.ScreenUpdating = True

End Sub


Sub CompareNumbers2()

Dim arr1()
Dim arr2()
Dim LR As Long
Dim i As Long
Dim c1 As Byte
Dim c2 As Byte
Dim counter As Byte
Dim lFoundCounter As Long

LR = Cells(65536, 2).End(xlUp).Row

LR = 14

arr1 = Range(Cells(5, 2), Cells(5, 8))
arr2 = Range(Cells(6, 2), Cells(LR, 8))

lStartTime = timeGetTime()

For i = 1 To LR - 5
counter = 0
For c1 = 1 To 7
For c2 = 1 To 7
'count and format matched number, move to next number in old
numbers

'-------------------------------------------------------------------
If arr1(1, c1) = arr2(i, c2) Then
Cells(i + 5, c2 + 1).Font.ColorIndex = 3
counter = counter + 1
Exit For
End If
Next
'found match, format new numbers, move to next new numbers
'---------------------------------------------------------
If counter 2 Then
lFoundCounter = lFoundCounter + 1
Range(Cells(i + 5, 2), Cells(i + 5, 8)).Font.Bold = True
Exit For
End If
Next
'back to normal format if no match in new numbers
'------------------------------------------------
If counter < 3 Then
With Range(Cells(i + 5, 2), Cells(i + 5, 8)).Font
.Bold = False
.ColorIndex = 1
End With
End If
Next

lEndTime = timeGetTime()

MsgBox "Done in " & lEndTime - lStartTime & " msecs", , ""

MsgBox lFoundCounter & " matches found", , ""

End Sub


Sub NoBorder(rng As Range, Optional wSh As Worksheet)

'clears any border from the passed range
'---------------------------------------

Dim sh As Worksheet

If wSh Is Nothing Then
Set sh = ActiveWorkbook.ActiveSheet
Else
Set sh = wSh
End If

With sh.Range(rng.Address)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

End Sub


Sub MediumBorder(rng As Range, Optional wSh As Worksheet)

'puts a medium border around the passed range
'--------------------------------------------

Dim sh As Worksheet

If wSh Is Nothing Then
Set sh = ActiveWorkbook.ActiveSheet
Else
Set sh = wSh
End If

With sh.Range(rng.Address)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

End Sub


Sub CopyBack()

'just for restoring the old range
'--------------------------------

Range(Cells(5, 17), Cells(35000, 23)).Copy Cells(5, 2)

End Sub



RBS

"knoertje " wrote in message
...
Of course I'm still interested. I don't fill in any lotery forms. We all
have sense enough to know that there is no way to beat any lotery
system. I do just want to keep track off the numbers. How often does a
number show up, and in combination with what other number, do odd
numbers show up more than even numbers, that kind of stuff. More a kind
of statistics. Some people collect stamps en some
others.............program in VBA.
I already have a few formula's that do some counting and how long ago
it was a number showed up. Now I like to see of some show up together
more often than others.
I go to my work every day, 5 days a week, week in and week out, month
in, month out etc........You know what waste that is???


---
Message posted from http://www.ExcelForum.com/

in my country we have a lottery of 5 numbers out of 42.
I use Access to store the data for past draws and then
make a number of different analysis. In my opinion Access
is a better tool then a spreadsheet to keep data.

Vince


-----Original Message-----
As a beginner with Excel and VBA I have the following
problem. I do keep
track of lottery numbers in a sheet.

Don't ask me why. It doesn't help me win a big price.
There is a draw
of six numbers every week, from 1 to 45. I

keep them in six collumns. From the year 1996 to this
week. I'd like to
seek combinations of numbers, say 2 or 3

numbers. If I, for example seek 3 numbers, 4-5-12 then
they should show
up highlighted or coloured or whatever,

if they excist in that combination. Or else give a
statement that the
combination doesn't excist.
I have tried something with conditional formating but I
can't seem to
get that to do what I want. Is there

anybody who can give me a hint!!!


---
Message posted from http://www.ExcelForum.com/

.

Unless you want to look at more than 65 thousand old
numbers, I can't see much wrong with a spreadsheet.
It all depends on what you are most familiar with.

RBS


wrote in message
...
in my country we have a lottery of 5 numbers out of 42.
I use Access to store the data for past draws and then
make a number of different analysis. In my opinion Access
is a better tool then a spreadsheet to keep data.

Vince


-----Original Message-----
As a beginner with Excel and VBA I have the following
problem. I do keep
track of lottery numbers in a sheet.

Don't ask me why. It doesn't help me win a big price.
There is a draw
of six numbers every week, from 1 to 45. I

keep them in six collumns. From the year 1996 to this
week. I'd like to
seek combinations of numbers, say 2 or 3

numbers. If I, for example seek 3 numbers, 4-5-12 then
they should show
up highlighted or coloured or whatever,

if they excist in that combination. Or else give a
statement that the
combination doesn't excist.
I have tried something with conditional formating but I
can't seem to
get that to do what I want. Is there

anybody who can give me a hint!!!


---
Message posted from http://www.ExcelForum.com/

.

Just wondering if after all this you got it to work, either with the
formula's or with VBA.

RBS


"knoertje " wrote in message
...
As a beginner with Excel and VBA I have the following problem. I do keep
track of lottery numbers in a sheet.

Don't ask me why. It doesn't help me win a big price. There is a draw
of six numbers every week, from 1 to 45. I

keep them in six collumns. From the year 1996 to this week. I'd like to
seek combinations of numbers, say 2 or 3

numbers. If I, for example seek 3 numbers, 4-5-12 then they should show
up highlighted or coloured or whatever,

if they excist in that combination. Or else give a statement that the
combination doesn't excist.
I have tried something with conditional formating but I can't seem to
get that to do what I want. Is there

anybody who can give me a hint!!!


---
Message posted from http://www.ExcelForum.com/

No, not yet. I'm busy truying to get it to work, but it does only a fe
lines of checking. And it takes a long time. But I keep trying. I wi
attach a zip file with what I have so far. I trying to choose between
or 3 or 4 number combinations. For example: I want to find out if 10 an
20 excist in combination with each other. Or if 2 and 12 and 37 exist i
combination with each other. And if they do, how often. Or even a fou
numbered combination. I don't know if it is possible in Excel, bu
that's what I had in mind. And maybe I get there someday. And by th
way.....thanks for the time and effort you have put into it

Attachment filename: testlotto.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=63726
--
Message posted from http://www.ExcelForum.com

Don't understand.
Have tested comparing a combination of 7 numbers with 35000 other 7 number
combinations.
This took about 1.5 secs. This is on a fast computer, but still.
If you want I send you the workbook.

RBS

"knoertje " wrote in message
...
No, not yet. I'm busy truying to get it to work, but it does only a few
lines of checking. And it takes a long time. But I keep trying. I wil
attach a zip file with what I have so far. I trying to choose between 2
or 3 or 4 number combinations. For example: I want to find out if 10 and
20 excist in combination with each other. Or if 2 and 12 and 37 exist in
combination with each other. And if they do, how often. Or even a four
numbered combination. I don't know if it is possible in Excel, but
that's what I had in mind. And maybe I get there someday. And by the
way.....thanks for the time and effort you have put into it.

Attachment filename: testlotto.zip
Download attachment:
http://www.excelforum.com/attachment.php?postid=637263
---
Message posted from http://www.ExcelForum.com/

It would be nice to take a look at that workbook. As you understand I'
just beginning with Excel and VBA. So learning from some code is alway
a good option

--
Message posted from http://www.ExcelForum.com

Ok, not sure though what the e-mail address is.

RBS

"knoertje " wrote in message
...
It would be nice to take a look at that workbook. As you understand I'm
just beginning with Excel and VBA. So learning from some code is always
a good option.


---
Message posted from http://www.ExcelForum.com/

In this attachment

Attachment filename: test.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=63969
--
Message posted from http://www.ExcelForum.com

What I mean is I need an e-mail address to send you the file.

RBS

"knoertje " wrote in message
...
In this attachment.

Attachment filename: test.zip
Download attachment:
http://www.excelforum.com/attachment.php?postid=639694
---
Message posted from http://www.ExcelForum.com/

As I don't want to leave my emailadress all over the place, to preven
my mailbox being flooded, I included it in the last attachement
test.zip. There you have to look!!!

--
Message posted from http://www.ExcelForum.com

I am not keen on downloading attachements!
My e-mail shows in this post (properties), so you
could just mail it to that.

RBS

"knoertje " wrote in message
...
As I don't want to leave my emailadress all over the place, to prevent
my mailbox being flooded, I included it in the last attachement,
test.zip. There you have to look!!!!


---
Message posted from http://www.ExcelForum.com/

Yours doen't show either. Anyway:

--
Message posted from http://www.ExcelForum.com

Well I must thank you for sending this so quickly. It does what I wan
it to do. And in good speed. I'm gratefull for the help. I hope I ca
be of any help sometime.

Thanks again

M

--
Message posted from http://www.ExcelForum.com

Half of the jackpot will do.

RBS


"knoertje " wrote in message
...
Well I must thank you for sending this so quickly. It does what I want
it to do. And in good speed. I'm gratefull for the help. I hope I can
be of any help sometime.

Thanks again

MJ


---
Message posted from http://www.ExcelForum.com/

Hello RB Smissaert,

Hope you don't mind me resurrecting this August 2004, Posting from
Excelforum.com/attachment.php?postid=637263 -
regarding "combination of numbers in lottery" dated 8/7/2004. I have a
similar requirement and if you do not mind I would appreciate a copy of the
Excel spreadsheet you created for MJ - knoertje, if still available.

Thanks
Tina

"RB Smissaert" wrote:

Don't understand.
Have tested comparing a combination of 7 numbers with 35000 other 7 number
combinations.
This took about 1.5 secs. This is on a fast computer, but still.
If you want I send you the workbook.

RBS

"knoertje " wrote in message
...
No, not yet. I'm busy truying to get it to work, but it does only a few
lines of checking. And it takes a long time. But I keep trying. I wil
attach a zip file with what I have so far. I trying to choose between 2
or 3 or 4 number combinations. For example: I want to find out if 10 and
20 excist in combination with each other. Or if 2 and 12 and 37 exist in
combination with each other. And if they do, how often. Or even a four
numbered combination. I don't know if it is possible in Excel, but
that's what I had in mind. And maybe I get there someday. And by the
way.....thanks for the time and effort you have put into it.

Attachment filename: testlotto.zip
Download attachment:
http://www.excelforum.com/attachment.php?postid=637263
---
Message posted from http://www.ExcelForum.com/

Hi Bart,

Thanks for file.
Tina


"Tina" wrote:

Hello RB Smissaert,

Hope you don't mind me resurrecting this August 2004, Posting from
Excelforum.com/attachment.php?postid=637263 -
regarding "combination of numbers in lottery" dated 8/7/2004. I have a
similar requirement and if you do not mind I would appreciate a copy of the
Excel spreadsheet you created for MJ - knoertje, if still available.

Thanks
Tina

"RB Smissaert" wrote:

Don't understand.
Have tested comparing a combination of 7 numbers with 35000 other 7 number
combinations.
This took about 1.5 secs. This is on a fast computer, but still.
If you want I send you the workbook.

RBS

"knoertje " wrote in message
...
No, not yet. I'm busy truying to get it to work, but it does only a few
lines of checking. And it takes a long time. But I keep trying. I wil
attach a zip file with what I have so far. I trying to choose between 2
or 3 or 4 number combinations. For example: I want to find out if 10 and
20 excist in combination with each other. Or if 2 and 12 and 37 exist in
combination with each other. And if they do, how often. Or even a four
numbered combination. I don't know if it is possible in Excel, but
that's what I had in mind. And maybe I get there someday. And by the
way.....thanks for the time and effort you have put into it.

Attachment filename: testlotto.zip
Download attachment:
http://www.excelforum.com/attachment.php?postid=637263
---
Message posted from http://www.ExcelForum.com/