Generate number combinations without duplicate pairings

Hi,
I need a VBA code to generate 5 number combinations from 1 to 50 without repeating pairs, order does not matter (ex. combination 1 2 3 4 5, number 1 cannot repeated with numbers 2 3 4 5 in the next combinations)
darren doAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Try this workbook. Click the 'Generate Pairs' button.
29091733.xlsm
0
 
Martin LissOlder than dirtCommented:
Please show a few examples of what you want.
0
 
darren doAuthor Commented:
Let's say two combinations 5 8 11 18 20 and 5 7 11 17 21 thats not good because the pair 5 and 11 is repeating I wanna generate combinations without such repeats
Thanks
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
This will generate 50 of them. Change the 50's in line 8 and 15 to generate more or less.
Sub RandomPairs()

Dim colR As New Collection
Dim intNum As Integer

Randomize
    
Do Until colR.Count = 50
    intNum = Int(50 * Rnd) + 1
    On Error Resume Next
    colR.Add intNum, Str(intNum)
    On Error GoTo 0
Loop

For intNum = 1 To 50
    Debug.Print colR(intNum)
Next
End Sub

Open in new window

1
 
Fabrice LambertFabrice LambertCommented:
Hi,

Maybe with the help of a collection (knowing the key must be unique):
Public Function getSerie() As Collection
On Error GoTo Error
    Dim col As Collection
    Set col = New Collection
    
    Randomize
    Dim i As Integer
    For i = 1 To 5
        Dim number As Integer
tryAgain:
        number = Int(50 * Rnd) + 1
        col.Add number, CStr(number)
    Next
    Set getSerie = col
Exit Function
Error:
    Resume tryAgain
End Function

Open in new window

0
 
Martin LissOlder than dirtCommented:
Fabrice, what advantage does yours have over mine?
0
 
Fabrice LambertFabrice LambertCommented:
Only the fact that it is a function returning the collection.

Note: Your solution wasn't showing up when I posted.
0
 
darren doAuthor Commented:
Martin Liss, thank you for your help.

But how do I get it working?

Thanks
0
 
Fabrice LambertFabrice LambertCommented:
No offences Martin, but I don't think you should take all credit for this.
0
 
Martin LissOlder than dirtCommented:
No offense taken.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.