# Permutation/Combination Formula Help

#### ASowinski

##### New Member
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!

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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)?

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!

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")
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")
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")
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")
Next colA
Next colB
Next colC
Next colD
Next colE

MsgBox "done"
End Sub``````

Replies
10
Views
284
Replies
6
Views
374
Replies
1
Views
112
Replies
5
Views
720
Replies
12
Views
311

1,217,451
Messages
6,136,714
Members
450,025
Latest member
Beginner52

### 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.

### Which adblocker are you using?

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

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