Excel VBA Combinations/Permutations

A

andrew_sampson

New Member
Joined
Dec 14, 2009
Messages
9
Hi Guys,

I'm really struggling to get the answer to the following question and hope you can help.

I have a list with items 1, 2 and 3.

How can I create a list that gives the following results: 1, 2, 3, 12, 13, 23 & 123

I know I can just use 3 For loops but I want the code to be able to cope with x numbers in the list. For example 1, 2, 3, 4 and 5 or 1, 2, 3, 4, 5, 6, 7, 8 and 9

Any help would be greatly appreciated
 
Sort by date Sort by votes
1) Do you care about the probability of each number's occurrence? In other words, would each number have the same likelihood as others of occurring? If so, create a unique value list of your values using a unique filter in Excel. If not, leave it as-is.

2) Can the pairs be different from what you have listed? For example can a good pair be 330/10?
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
No, each number can have its number of occurring depending on the permutation and no the pairs are just what I have listed
 
Upvote 0
Off the top of my head, I would do a random number between 1 and the number of pairs you want to pick from (so between 1 and 58 in your sample/question) using this:
https://www.techonthenet.com/excel/formulas/rnd.php

Create a do while loop that does that until you have count of "good" pairs (9) you want. In that loop I use the rnd link above to generate a number (1 to 58). Use that number to index the corresponding pair in you list. Concatenate it using some like this = rng.offset(0,1) & "/" & rng.offset(0,2) (assuming rng is the range of you index 1 to 58). Before you store it in a "good" list in the spreadsheet check that it doesn't already exist using rng.Find(..etc...). If it doesn't exist add it to your list of "good" pairs and increase an increment variable by 1. If the pair already exists on your "good" list, don't increment and skip putting it on your "good" list and loop again to find another rnd number/pair index (1 to 58) and corresponding range (rng) and pair.

Try to write code for that and I'll try to give you some pointers. Sorry, I am working and can't do it all for you.
 
Last edited:
Upvote 0
pgc01 said:
Hi Andrew
Welcome to the board

You want to calculate the Power Set of a Set.

You can try this code:

Code: 
Option Explicit
 
' PGC Oct 2007
' Calculates a Power Set
' Set in A1, down. Result in C1, down and accross. Clears C:Z.
Sub PowerSet()
Dim vElements As Variant, vresult As Variant
Dim lRow As Long, i As Long
 
vElements = Application.Transpose(Range("A1", Range("A1").End(xlDown)))
Columns("C:Z").Clear
 
lRow = 1
For i = 1 To UBound(vElements)
    ReDim vresult(1 To i)
    Call CombinationsNP(vElements, i, vresult, lRow, 1, 1)
Next i
End Sub
 
Sub CombinationsNP(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Long
 
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        lRow = lRow + 1
        Range("C" & lRow).Resize(, p) = vresult
    Else
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    End If
Next i
End Sub

Test:

- Write a, b, c, d in A1:A4
- run PowerSet


[TABLE="width: 2"]
<tbody>[TR]
[TH][/TH]
[TH="width: 30, align: center"]A[/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[TH="width: 30, align: center"]G[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]d[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 8"][Book1]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Dear Mr. PGC01,
I'm a new member.
I has a question which is familiar with this post and hope you can help.
I have a list with items 1, 2, 3, 4, ..., 10.
How can I create a list that gives the following results: 1 -2 , 1 - 3, 1-4, 1-2-3, 2-3-4... (each number is in a cell). The result's condition is the total is less than 10. The condition will be taken from a inputbox.
I'm look forward to hearing from you.
Best regard.
 
Upvote 0
I need a macro that is almost theses, I've numbers and I need the permutation in a specific amount, for example, the list of numbers is 1 to 25, and i need permutate theses numbers in group of 14 numbers, all of then, (1-2-3-4-5-6-7-8-9-10-11-12-13-14), (1-2-3-4-5-6-7-8-9-10-11-12-13-15), (1-2-3-4-5-6-7-8-9-10-11-12-13-16)...
I appreciated the help.
 
Upvote 0
Hi All,

I am new user and I have some similar requirement. I need permutation of A, B, C and D but I also need to specify maximum how many times each letter can occur.
i.e I need A to appear max 4 times, B 2 times, C 2 time and D 2 times. Valid examples AAAA, AAAB, AABB, AACC, CCDD (since these are within limits). Invalid examples ABBB, ACCC, ADDD, CCC, CCCA (because B,C,D appeared more than 2 time)
Is there an easy way to do this? Any help/direction would be appreciated.
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
saumyar, This thread is over 2 years old.
You would get better, faster responses if you started you own thread about your problem specifically.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Combinations Permutations_Variable Analysis
Replies
1
Views
648
cotech_10
C
I
  • Question Question
Select Listbox value to be then taken to customer on worksheet
Replies
1
Views
104
ipbr21054
I
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
231
campbell1093
C
I
  • Solved
Also have the row number shown in Listbox on userform advice
Replies
7
Views
75
NoSparks
N
S
  • Question Question
Excel Formula to build out Combinations/Permutations of a series of numbers
Replies
1
Views
197
Eric W
Eric W

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

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
Back
Top