Permutation/Combination Formula Help

ASowinski

New Member
Joined
Dec 14, 2016
Messages
3
In my sheet I have Columns ranging from... A2:A4, B2:B7, only C2, D2:D8, and E2:E1679

Is it possible to write a formula to get all the permutations in individual cells??

Eg,

A2, A2B2, A2B2C2.....A3, A3B3........A4B7C2D8E169??

Thanks!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Welcome to the board.

This is not very hard with a macro, but we need some more information.

Your examples all imply that your permutations would only take one cell from each column and that each permutation has to have a member from each column to the left, but not necessarily each column to the right. Is that correct? Or could you have A2A3, just B2, or A2C2?

Do you want the permutations to return just the cell addresses? Or do you want the cell values separated with some kid of delimiter (e.g. 1_2 where the underscore separates the cells' values)?
 

ASowinski

New Member
Joined
Dec 14, 2016
Messages
3
Thanks for the reply Oaktree!

That is correct, each permutation must contain a member from each column to the left, not necessarily the right. Column A should only be used once per permutation, eg. no A2A3, A2A4, etc.

And they can be returned all as one number in each cell. They do not need to be separated.

Does that make sense?
Thanks again!
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
There are more elegant ways to do this, but I wanted to give you something to work with before I'm stuck in meetings all day. This will list them in column G.

Code:
Option Explicit

Sub ListCombinations()
Dim colA, colB, colC, colD, colE

Range("G:G").EntireColumn.ClearContents

'1 dimension
For Each colA In Range("A2:A4")

Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) = colA.Address(False, False)

Next colA

'2 dimensions
For Each colB In Range("B2:B7")
    For Each colA In Range("A2:A4")
        Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) = colA.Address(False, False) & colB.Address(False, False)
    Next colA
Next colB

'3 dimensions
For Each colC In Range("C2")
    For Each colB In Range("B2:B7")
        For Each colA In Range("A2:A4")
            Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) = colA.Address(False, False) & colB.Address(False, False) & colC.Address(False, False)
        Next colA
    Next colB
Next colC
        
'4 dimensions
For Each colD In Range("D2:D8")
    For Each colC In Range("C2")
        For Each colB In Range("B2:B7")
            For Each colA In Range("A2:A4")
                Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) = colA.Address(False, False) & colB.Address(False, False) & colC.Address(False, False) & colD.Address(False, False)
            Next colA
        Next colB
    Next colC
Next colD

'5 dimensions
For Each colE In Range("E2:E1697")
    For Each colD In Range("D2:D8")
        For Each colC In Range("C2")
            For Each colB In Range("B2:B7")
                For Each colA In Range("A2:A4")
                    Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) = colA.Address(False, False) & colB.Address(False, False) & colC.Address(False, False) & colD.Address(False, False) & colE.Address(False, False)
                Next colA
            Next colB
        Next colC
    Next colD
Next colE

MsgBox "done"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top