All possible permutation/combinations

[Dr. Demento]

Good morning!

Forgive me, but I don't remember the difference between permutations and combinations, so hopefully my explanation points in the proper direction.

I have two ordered lists (NORTH|SOUTH|EAST|WEST) and (N|S|E|W) and I want to make a listing of all permutations/combinations?? substituting N for NORTH, S for SOUTH, etc. The order of the values is fixed as shown.

For example, if I start with the first row, I want all possible perms/combos with both the full word and the abbreviation, as shown with the next four lines.

|-------|-------|-------|-------|
| NORTH | SOUTH | EAST | WEST |
|-------|-------|-------|-------|
| N | SOUTH | EAST | WEST |
|-------|-------|-------|-------|
| N | S | EAST | WEST |
|-------|-------|-------|-------|
| N | S | E | WEST |
|-------|-------|-------|-------|
| N | S | E | W |
|-------|-------|-------|-------|

This is just a partial example; I was trying to manually jam this out and kept getting screwed up.

Any help would be much appreciated.

Thanks y'all.

 

[Dr. Demento]

Yes @johnnyL that is exactly right.

There are only 16 answers; it's a substitution permutation. Fluff & Eric W show the proper answers.

 

[johnnyL]

So it's not a permutation as I understand permutation.

When I think of permutation I think of it like a phone number, in your case, it would be only 4 long though.
If you were to "dial" 'NESW' well that would be different than 'NSEW'.

In your case you have 8 different items taken 4 at a time. The excel formula for that is
=PERMUT(8,4)
which equates to 1680.

I guess I wasn't in tune to what you were looking for.

 

[Dr. Demento]

You're correct. I couldn't remember which was which, but it appears I was looking for a substitution combination.

Appreciate it.

 

[Fluff]

With the same setup as post#9 how about

Sub DrDemento()
   Dim Rng As Range
   Dim r As Long, c As Long
   Dim a As Variant, b As Variant
   
   Set Rng = Range("A1:D2")
   r = 2
   c = 4
   a = Evaluate("mid(base(transpose(row(1:" & r ^ c & ")-1)," & r & "," & c & "),row(1:" & c & "),1)+1")
   b = Application.Index(Rng.Value, Application.Transpose(a), Array(1, 2, 3, 4))
   Range("A5").Resize(UBound(b), UBound(b, 2)).Value = b
End Sub

 

[Dr. Demento]

Thank you, Fluff, it doesn't seem to work.

b is a single dimension array and when i change UBound(b, 2) to UBound(b) in the final line, I get four copies of the top row. I understand why I get that result but I'm not sure what a and b do.

Thanks much.

d

 

[Fluff]

If you step through the code when you get to the line b = Application.Index have a look n the locals window & is a an array 1 to 4, 1 to 16?

 

[Dr. Demento]

No, a = 1

 

[Fluff]

Thanks for that, it looks as though the evaluate isn't working in your version. Try it like this instead

Sub DrDemento()
   Dim rng As Range
   Dim r As Long, c As Long
   Dim a As Variant, b As Variant
   
   Set rng = Range("A1:D2")
   r = 2
   c = 4
   a = Evaluate("if({1},mid(base(transpose(row(1:" & r ^ c & ")-1)," & r & "," & c & "),row(1:" & c & "),1)+1)")
   b = Application.Index(rng.Value, Application.Transpose(a), Array(1, 2, 3, 4))
   Range("A5").Resize(UBound(b), UBound(b, 2)).Value = b
End Sub

 

[Dr. Demento]

That works awesome, Fluff! Thanks so much!!

Out of curiosity, how do I convert your subt to use a two 1D arrays (or a single 2D array) as input instead of a range?

Thanks again.

 

[Worf]

This thread may be of interest:

Combination Help

Hey guys- I have a list of 10 of my favorite/lucky numbers that I want to play in the lottery. The lottery picks 5 numbers total. I need a way to show me all the possible combinations of my 10 numbers picked in a 5 number draw (hope that makes sense). There are no repeat combinations- for...

www.mrexcel.com