Group Number By Using Permutation and Combination in VBA Excel

Question

Suppose that we have N number or data and we want to put those number or data into a group that contains k number. For example: let A be a set of number {1, 2, 3} and I want to put them into groups that contains 2 number. If I use permutation, I will get {1,2}, {1,3}, {2,1}, {2,3}, {3,1}, and {3,2}. If I use combination, I will get {1,2}, {1,3}, and {2,3}. I want each group is placed in different row and each number in different cells, for example: {1,2} in first row (cells(1,1) = 1 and cells(1,2) = 2), and so on like the example below.

1 | 2

1 | 3

2 | 1

2 | 3

3 | 1

3 | 2

How to make a VBA Excel program to do thing such that?

Gary's Student · Accepted Answer · 2014-02-05 13:06:52Z

1

This is a partial answer. This small macro will list the permutations of N values taken two at a time:

Sub Permutations()
    Dim N As Long, i As Long, j As Long, rr As Long
    N = Application.InputBox(Prompt:="Enter N", Type:=1)
    rr = 1
    For i = 1 To N
        For j = 1 To N
            Cells(rr, 1) = i
            Cells(rr, 2) = j
            rr = rr + 1
        Next j
    Next i
End Sub

If you need more than two items per group, modify the code to add loops as necessary. If you need the groups to consist of items other than simple numbers, use the results of the macro to drive VLOOKUP() columns.

NOTE:

If you need to eliminate duplicates, then use this version:

Sub Permutations2()
    Dim N As Long, i As Long, j As Long, rr As Long
    N = Application.InputBox(Prompt:="Enter N", Type:=1)
    rr = 1
    For i = 1 To N
        For j = 1 To N
            If i <> j Then
                Cells(rr, 1) = i
                Cells(rr, 2) = j
                rr = rr + 1
            End If
        Next j
    Next i
End Sub

edited Feb 5, 2014 at 13:06

answered Feb 5, 2014 at 12:58

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tunk-Fey Over a year ago

Thanks for your code Gary. But how if I have a set data {4,5,8,9,19} and I want to put them into groups where each group contains 3 or maybe 4 data? Your code only works for consecutive set numbers.

Collectives™ on Stack Overflow

Group Number By Using Permutation and Combination in VBA Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related