# Thread: how do i check 2 sets of data? (Excel 2000)

1. ## how do i check 2 sets of data? (Excel 2000)

I'm making a spreadsheet to help me with the lottery. Firstly i want to randomly create 20 rows of 6 numbers each (i've used 'randbetween' but some of the numbers are duplicated so how do i stop this? And secondly, when the actual numbers are drawn i want to be able to enter them onto my spreadsheet and have it check the drawn numbers with my 20 rows of numbers and it to tell me how many of the drawn numbers i have on each line? Does that make sense? Any help with this would be much appreciated cos it's doing my head in. Cheers Richie.

2. ## Re: how do i check 2 sets of data? (Excel 2000)

This VBA routine will generate 20 rows of random numbers with no numbers repeated in the same row.

<pre>Public Sub GetRand()
Dim iUsed(1 To 6) As Integer
Dim I As Integer, J As Integer, K As Integer, iRand As Integer
Randomize
For I = 0 To 19
J = 1
Do While J <= 6
iRand = Int(50 * Rnd + 1)
For K = 1 To 6
If J = K Then
iUsed(J) = iRand
J = J + 1
Exit For
End If
If iRand = iUsed(K) Then
Exit For
End If
Next K
Loop
For J = 1 To 6
Worksheets("Sheet1").Range("A1").Offset(I, J - 1) = iUsed(J)
iUsed(J) = 0
Next J
Next I
End Sub
</pre>

I would need a little better description of how you want to compare and count to answer the second half of your question.

3. ## Re: how do i check 2 sets of data? (Excel 2000)

The following prcedure wil fill a 20x6 range with random (unique in each row) numbers <pre>Const TotNumbers = 42
Sub SelectLottoNumbers()
Dim I As Integer, J As Integer, K As Integer
Dim PickFrom(TotNumbers) As Integer
Dim PickNum As Integer
Randomize
Range("C3:H22").ClearContents
Application.ScreenUpdating = False
For I = 0 To 19
For K = 1 To TotNumbers
PickFrom(K) = K
Next
For J = 0 To 5
Picked = False
Do While Not Picked
PickNum = Int(Rnd * TotNumbers) + 1
If PickFrom(PickNum) <> 0 Then
PickFrom(PickNum) = 0
Picked = True
Worksheets("Lotto").Range("c3").Offset(I, J).Value = PickNum
End If
Loop
Next J
Next I
Application.ScreenUpdating = True
End Sub</pre>

As I have no idea of the numbers in your lottery, I have included a constant at the top of the code which you can change as required.

I attach a workbook with the above and also some formulas so that you can compare the numbers generated with a given set of 6 numbers (C2:H2). Rows I to N are used for this purpose and are hidden on the sheet.

Hope it works.

4. ## Re: how do i check 2 sets of data? (Excel 2000)

I have amended the previous posted file to use an array Formula in I3:I22, to count the matches, which eliminates the need for hidden columns, and as there are only 20 rows there should be no appreciable performance hit.

If you in any way modify those formulas (and it is easy to do accidentally), you will need to re-enter them by pressing CTRL-SHIFT-ENTER at the same time.

File attached

Andrew C

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•