Create permutations [UDF]
Maximum Number Allowed is 4 digit and the number is from 0 to 9.
After I fill in the number i want it to automatic permutate the numbers and list in details, example if i key in 1234 and the list will be:
1234, 1243, 1423, 4123, 1324, 1342, 1432, 4132, 3124, 3142, 3412, 4312, 2134, 2143, 2413, 4213, 2314, 2341, 2431, 4231, 3214, 3241, 3421, 4321.
Answer:
This udf creates permutations from a text string. You can also choose how many letters in each permutation.
Array formula in cell A3:A26:
How to create this array formula
- Select cell range A3:A26
- Type above array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys
VBA code
Function ListPermut(str As String, num As Integer) 'Permutations without repetition Dim c, r, p As Long Dim rng() As Long, temp As Long, i As Long Dim temp1 As Long, y() As Long, d As Long Dim tmpOut(), tmpArr() As Variant Dim j As Integer Dim a As Boolean ReDim tmpArr(0) ReDim tmpOut(0) For j = 1 To Len(str) tmpArr(UBound(tmpArr)) = Mid(str, j, 1) ReDim Preserve tmpArr(UBound(tmpArr) + 1) Next j ReDim Preserve tmpArr(UBound(tmpArr) - 1) p = WorksheetFunction.Permut(Len(str), Len(str)) ReDim rng(1 To p, 1 To Len(str)) For c = 1 To Len(str) rng(1, c) = c Next c For r = 2 To p For c = 1 To num tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut)) & tmpArr(rng(r - 1, c) - 1) Next c If UBound(tmpOut) <> 0 Then If tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut) - 1) Then tmpOut(UBound(tmpOut)) = "" Else ReDim Preserve tmpOut(UBound(tmpOut) + 1) End If Else ReDim Preserve tmpOut(UBound(tmpOut) + 1) End If For c = Len(str) To 1 Step -1 If rng(r - 1, c - 1) < rng(r - 1, c) Then temp = c - 1 Exit For End If Next c For c = Len(str) To 1 Step -1 rng(r, c) = rng(r - 1, c) Next c For c = Len(str) To 1 Step -1 If rng(r - 1, c) > rng(r - 1, temp) Then temp1 = rng(r - 1, temp) rng(r, temp) = rng(r - 1, c) rng(r, c) = temp1 ReDim y(Len(str) - temp) i = 0 For d = temp + 1 To Len(str) y(i) = rng(r, d) i = i + 1 Next d i = 0 For d = Len(str) To temp + 1 Step -1 rng(r, d) = y(i) i = i + 1 Next d Exit For End If Next c If r = p Then For c = 1 To num tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut)) & tmpArr(rng(r, c) - 1) Next c If tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut) - 1) Then ReDim Preserve tmpOut(UBound(tmpOut) - 1) End If End If Next r ListPermut = Application.Transpose(tmpOut) End Function
Where to copy vba code?
Press Alt+F11
Permutations category
Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
Excel categories
8 Responses to “Create permutations [UDF]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
i am trying / looking for a way to write in-excel 2007, 20 numbers out of 1 to 45 like Australian lotto ,there is a site that you probaly know...will give 4 out of 4 numbers while seleecting up to 12 picked.is the a way I can show 45 numbers ie 1 to 45, then use hlookup, and get it to show the cells for the first 6 out of the 45, and so on..
thanks Bert
bert
Can you explain in greater detail?
looks like there's a syntax error with the line starting If rng(r - 1, c - 1) < rng(r - 1, c) Then..... unable to execute the code
Thanks for making your code accessible to everyone. Super helpful!
Kayla
Thank you for telling me, I hope it works now.
In case it is not working, there is a file you can try out.
Hi Oscar,
Can you give a file that i can try out for this code?
Thank you. One more question on this code. I need 3 out 5 unique permutations (e.g. 3 buttons out total 5 are selected). your code allows to list possible permutations for a given number, but not for any selection of numbers out of given numbers).
Let say, I have 5 buttons (A, B, C, D & E) and select 3 unique buttons so 60 total unique combinations are available. Not sure how to modify your code to list this in Excel.
Hi Oscar, this works great for up to 8 digits, for example =ListPermut("12345678",8) generates the correct 40,320 permutations. However for 9 digits with =ListPermut("123456789",9) it stops generating after 35,200 permutations however the correct count should be 362,880. I don't see anything in the VBA code that should cause it to stop early. Any ideas or fixes???? Thanks!
Dear Oscar,
Thanks for generator of this permutation.
All the list of permutation are list in the array of column. What if I want it list in the array of row instead? What part should I edit in your module code?
with regards,