Results 1 to 4 of 4
  1. #1
    dalamar33
    Guest

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

Posting Permissions

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