Excel permutations without repetition from values in multiple columns

Question

What I have in Excel document:

A     B    
Abc   12:34
Def   56:78
Ghi   90:12
Jkl   34:56
...

What I want to achieve with those values:

C    D      E    F
Abc  12:34  Def  56:78
Abc  12:34  Ghi  90:12
Abc  12:34  Jkl  34:56
Def  56:78  Ghi  90:12
Def  56:78  Jkl  34:56
Ghi  90:12  Jkl  34:56
...

Explanation:

Columns A and B can contain any combination of text and numbers (if that is important at all), this example only displays the most common structure. It should create combinations only for rows "on the way down", i. e. "Abc...Def..." is enough, there shouldn't be "Def...Abc...".

There are many examples around, but I am struggling to find a version of such VBA that works with multiple columns and doesn't repeat combinations.

Here is one example that is simple. But, it's for only one column and it also repeats values:

http://www.mrexcel.com/forum/excel-questions/412952-create-list-all-pair-combinations.html#post2046893

Thank you in advance.

Given your example, this can be accomplished with a simple double for loop... no? — John Bustos
– John Bustos, Commented Mar 27, 2014 at 14:20
I really don't know, I am not an expert for Excel, but most of the answers for other questions about permutations involved macros. Also, some of my sheets will have 30-40 rows, so anything that involves selecting X number of rows that need to be filled afterwards isn't really practical. If that is how double for loop works in Excel at all. :) — take2
– take2, Commented Mar 27, 2014 at 14:24
No - The answer would involve programming / VBA - But what I'm sayig is that, if you look at your answer, you go down each row and combine it with all the rows AFTER it, then move down to the next and do the process again... Does that make sense?? — John Bustos
– John Bustos, Commented Mar 27, 2014 at 15:31
That's correct - each row gets combined only with the rows after it and you repeat the process until it gets to the last row. — take2
– take2, Commented Mar 27, 2014 at 16:12

John Bustos · Accepted Answer · 2014-03-27 16:23:07Z

2

Given what we discussed in the conversation, here's a solution in VBA:

Sub CreatePermutation()

Dim FirstCell As Integer
Dim SecondCell As Integer
Dim NumRows As Integer
Dim OutputRow As Long

    ' Get the total number of rows in column A
    NumRows = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row()

    ' You want to start outputting in row 1
    OutputRow = 1

    For FirstCell = 1 To NumRows - 1
        For SecondCell = FirstCell + 1 To NumRows

            ' Put in the data from the first cell into columnc C & D
            Cells(OutputRow, 3).Value = Cells(FirstCell, 1).Value
            Cells(OutputRow, 4).Value = Cells(FirstCell, 2).Value

            ' Put in the data from the second cell into column E & F
            Cells(OutputRow, 5).Value = Cells(SecondCell, 1).Value
            Cells(OutputRow, 6).Value = Cells(SecondCell, 2).Value

            ' Move to the next row to output
            OutputRow = OutputRow + 1

        Next SecondCell
    Next FirstCell
End Sub

Hope this accomplishes what you're looking to do.

answered Mar 27, 2014 at 16:23

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

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

3 Comments

take2 Over a year ago

Wow, you made it look really simple. :) That's exactly what I need, thank you!!

John Bustos Over a year ago

:) - Everything is simple when you've done it before! - Glad I could help!! :)

Alberto Brown Over a year ago

What would be the modification to expand this to 5 columns or 6 columns? I have a different permutation code I found but all of the repeat values hit the max number of rows in excel. Which may even still be an issue since my data sett is Col A,B,D,E,F are 1-22 ( without #5) and Col C is 1-22.

Collectives™ on Stack Overflow

Excel permutations without repetition from values in multiple columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related