×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# VBA permutation of arrays

 Forum Search FAQs Links MVPs

## VBA permutation of arrays

(OP)
Hello,

I have the following Table

Name Min Max Nvals
A -5 6.7 6
B -10 23 4
C 2 15 3

I am looking for a code that generates all the sets of [A B C] that include all the possible variatiosn as defined in the Table
The cath is that the code has to be able to cope with an apriori unknown number of variables , so nested loopes option seems to me unusable

Thank you

### RE: VBA permutation of arrays

Well, with no further constraints, there are infinite possibilities for each.

### RE: VBA permutation of arrays

You may also present your data correctly.
Is it:
  Name Min  Max Nvals
A  -5   6.7  6
B -10   23       4
C   2   15   3

or
Name Min  Max Nvals
A    -5   6.7  6
B   -10   23   4
C     2   15   3

  Name Min  Max Nvals
A  -5       6.7  6
B -10   23  4
C       2   15   3

  Name Min  Max Nvals
A      -5   6.7  6
B -10   23   4
C   2       15   3


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

### RE: VBA permutation of arrays

What do you mean with "all the sets of [A B C] that include all the possible variations" ?
Something like this or something else ?

#### CODE

Name	Min	Max	Nvals
A	-5	6.7	6
B	-10	23	4
C	2	15	3

A	-5	6.7	6
C	2	15	3
B	-10	23	4

B	-10	23	4
A	-5	6.7	6
C	2	15	3

B	-10	23	4
C	2	15	3
A	-5	6.7	6

C	2	15	3
A	-5	6.7	6
B	-10	23	4

C	2	15	3
B	-10	23	4
A	-5	6.7	6 

### RE: VBA permutation of arrays

The question is unclear

### RE: VBA permutation of arrays

(OP)
Here is a more detailed presentation of the question:

I have 3 variables A,B,C which can have repectively 6,4 and 3 possible values.

I need to create SETS of [A B C] that cover all the possible combinations of A, B and C values.

All this in VBA where the input is the NUMBER of VARIABLES (here 3) and the VARIABLES VALUES.

The INPUT data ---------------------------------------------------

Name Min Max Nvals
A -5 6.7 6 i.e. A=[5 11.7 18.4 25.1 31.8 38.5]
B -10 23 4 i.e. B=[10 33 56 79]
C 2 15 3 i.e. C=[2 17 32]

The total number of combinations(permutations) is 6x4x3=72

The output data (Comnination Sets) ------------------------------

no. A B C
1 5 10 2
2 5 33 2
3 5 56 2
4 5 79 2
5 11.7 10 2
6 11.7 33 2
.
.
.
72 38.5 79 32

### RE: VBA permutation of arrays

So,
A can have the values of 5 11.7 18.4 25.1 31.8 38.5
B can have the values of 10 33 56 79
C can have the values of 2 17 32
and the outcome you want is:
1-5-10-2
2-5-10-17
3-5-10-32
4-5-33-2
5-5-33-17
6-5-33-32
7-5-56-2
8-5-56-17
9-5-56-32
10-5-79-2
11-5-79-17
12-5-79-32
13-11.7-10-2
14-11.7-10-17
15-11.7-10-32
16-11.7-33-2
17-11.7-33-17
18-11.7-33-32
19-11.7-56-2
20-11.7-56-17
21-11.7-56-32
22-11.7-79-2
23-11.7-79-17
24-11.7-79-32
25-18.4-10-2
26-18.4-10-17
27-18.4-10-32
28-18.4-33-2
29-18.4-33-17
30-18.4-33-32
31-18.4-56-2
32-18.4-56-17
33-18.4-56-32
34-18.4-79-2
35-18.4-79-17
36-18.4-79-32
37-25.1-10-2
38-25.1-10-17
39-25.1-10-32
40-25.1-33-2
41-25.1-33-17
42-25.1-33-32
43-25.1-56-2
44-25.1-56-17
45-25.1-56-32
46-25.1-79-2
47-25.1-79-17
48-25.1-79-32
49-31.8-10-2
50-31.8-10-17
51-31.8-10-32
52-31.8-33-2
53-31.8-33-17
54-31.8-33-32
55-31.8-56-2
56-31.8-56-17
57-31.8-56-32
58-31.8-79-2
59-31.8-79-17
60-31.8-79-32
61-38.5-10-2
62-38.5-10-17
63-38.5-10-32
64-38.5-33-2
65-38.5-33-17
66-38.5-33-32
67-38.5-56-2
68-38.5-56-17
69-38.5-56-32
70-38.5-79-2
71-38.5-79-17
72-38.5-79-32


then you can simply do:

#### CODE

Option Explicit

Sub Katto()
Dim A() As String
Dim B() As String
Dim C() As String
Dim iA As Integer
Dim iB As Integer
Dim iC As Integer
Dim x As Integer

A = Split("5 11.7 18.4 25.1 31.8 38.5", " ")
B = Split("10 33 56 79", " ")
C = Split("2 17 32", " ")

For iA = 0 To UBound(A)
For iB = 0 To UBound(B)
For iC = 0 To UBound(C)
x = x + 1
Debug.Print x & "-" & A(iA) & "-" & B(iB) & "-" & C(iC)
Next iC
Next iB
Next iA

End Sub 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

### RE: VBA permutation of arrays

(OP)
Thanks, but, as I mentioned before, I need a script where the NUMBER OF Variables can be a variable ITSELF
In your code, there are 3 nested loops.
If another case requires 10 variable, I need to rewrite the code to accomodate 10 nested loops.
No big deal but, it limites the generality of the code.

I think, I need some kind of a recursive method. Not in expert in this

### RE: VBA permutation of arrays

What is the business requirement for this?
Or it is strictly 'academic' (homework) exercise?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

### RE: VBA permutation of arrays

(OP)
no business requirement. it's just a problem that I had before and can't stop trying to get an answer to it

### RE: VBA permutation of arrays

So now you have an example that can be easily modified to include more than 3 loops, for an application that is "just a problem that I had before and can't stop trying to get an answer to it."

Skip,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

### RE: VBA permutation of arrays

(OP)
you do understand that your solution is the trivial solution to the problem I stated, right?
It's not quite what I was looking for

### RE: VBA permutation of arrays

If we guess that Min is the minimum value of each set, Max is the maximum value of each set and Nvals the number of members in each set then every one of your examples fails your criteria.

Name Min Max Nvals
A -5 6.7 6 i.e. A=[5 11.7 18.4 25.1 31.8 38.5]
Min is not a member. Max is not a member. Five out of six values are larger than Max

B -10 23 4 i.e. B=[10 33 56 79]
Min is not a member. Max is not a member. Three out of four values are larger than Max

C 2 15 3 i.e. C=[2 17 32]
Max is not a member. Two out of three values are larger than Max

You do understand that coding is the systematic solution of a fully defined problem, right?

### RE: VBA permutation of arrays

This is called Cartesian Product https://en.wikipedia.org/wiki/Cartesian_product

To make the implementation universal - i.e. without need to have 3 loops for 3 arrays, 4 loops for 4 arrays,..etc - we can create 2 procedures:

1) procedure product_of_two:
which will have on input two 1D arrays and returns an array of arrays
This procedure computes only product of two arrays

2) procedure product_of_n
which will have on input array of arrays and will return other array of arrays
This procedure will use the previous procedure to process compound array given on input.

Unfortunatelly, i don't have now Windows machine by my hands, so i have done proof of concept on Linux using python, which uses lists instead of arrays.

katto.py

#### CODE

def product_of_two(list_1, list_2):
# input : two lists
# output: list of lists
lists = []
for x in list_1:
for y in list_2:
if type(x) == list:
# create new list from list x and element y
list_new = [e for e in x]
list_new.append(y)
lists.append(list_new)
else:
lists.append([x, y])
#
return lists

def product_of_n(input_lists):
# input : list of lists
# output: list of lists
lists = product_of_two(input_lists[0], input_lists[1])
for sublist in input_lists[2:]:
lists = product_of_two(lists, sublist)
#
return lists

if __name__ == "__main__":
a=[5, 11.7, 18.4, 25.1, 31.8, 38.5]
b=[10, 33, 56, 79]
c=[2, 17, 32]
d=[100, 200]

input_lists = [a, b, c]
#input_lists = [a, b, c, d]

output_lists = product_of_n(input_lists)

# print result
n = 0
for sublist in output_lists:
n += 1
print("%2d: %s" % (n, sublist)) 

With the data given above it delivers the same result as posted by Andrzejek

#### CODE

\$ python3 katto.py
1: [5, 10, 2]
2: [5, 10, 17]
3: [5, 10, 32]
4: [5, 33, 2]
...
...
69: [38.5, 56, 32]
70: [38.5, 79, 2]
71: [38.5, 79, 17]
72: [38.5, 79, 32] 

if you uncomment

#### CODE

input_lists = [a, b, c, d]
you will get Cartesian Product of 4 arrays without need to implement additional loop:

#### CODE

1: [5, 10, 2, 100]
2: [5, 10, 2, 200]
3: [5, 10, 17, 100]
4: [5, 10, 17, 200]
...
...
140: [38.5, 79, 2, 200]
141: [38.5, 79, 17, 100]
142: [38.5, 79, 17, 200]
143: [38.5, 79, 32, 100]
144: [38.5, 79, 32, 200] 

### RE: VBA permutation of arrays

I was thinking to suggest Python Itertools.Product, which would do this in no time.

But Katto is constrained to VBA.

### RE: VBA permutation of arrays

@mintjulep,
That's why I tried to do it without itertools so that it could then be written in VBA in the similar way.

### RE: VBA permutation of arrays

@mikrom

Have you looked inside iteertools, to see how they do it?

Edit:
Ok, I just peaked. Iteertools is implemented in c.

### RE: VBA permutation of arrays

Out of curiosity, I rewrote into VBA the python code posted before

#### CODE

' Cartesian Product

Function join_collection(list As Collection, separator As String) As String
join_collection = ""
For i = 1 To list.Count - 1
join_collection = join_collection & list.Item(i) & separator
Next i
join_collection = join_collection & list.Item(list.Count)
End Function

Function product_of_two(list_1 As Collection, list_2 As Collection) As Collection
'input : two lists
'output: list of lists
Set product_of_two = New Collection
For Each x In list_1
For Each y In list_2
'create new list from list x and element y
Set list_new = New Collection
If (TypeOf x Is Collection) Then
For Each e In x
Next e
Else
End If
Next y
Next x
'return list of lists
End Function

Function product_of_n(input_lists As Collection) As Collection
'input : list of lists
'output: list of lists

Dim lists As Collection
Set lists = product_of_two(input_lists.Item(1), input_lists.Item(2))
For i = 3 To input_lists.Count
Set lists = product_of_two(lists, input_lists.Item(i))
Next i
Set product_of_n = lists
End Function

Sub test()
Debug.Print "Test: Cartesian Product" & vbCrLf

Dim values() As Variant
Dim a As New Collection
Dim b As New Collection
Dim c As New Collection
Dim d As New Collection
Dim input_lists As New Collection
Dim output_lists As New Collection

values = Array(5, 11.7, 18.4, 25.1, 31.8, 38.5)

For Each Item In values
Next Item

values = Array(10, 33, 56, 79)

For Each Item In values
Next Item

values = Array(2, 17, 32)

For Each Item In values
Next Item

values = Array(100, 200)

For Each Item In values
Next Item

Set output_lists = product_of_n(input_lists)

'print result
n = 0
Dim sublist As Collection
For Each sublist In output_lists
n = n + 1
Debug.Print Right("   " & n, 3) & ": [" & join_collection(sublist, ", ") & "]"
Next sublist
End Sub 

Running the test makro prints in the immediate window the same result as before

#### CODE

Test: Cartesian Product

1: [5, 10, 2]
2: [5, 10, 17]
3: [5, 10, 32]
4: [5, 33, 2]
...
...
69: [38.5, 56, 32]
70: [38.5, 79, 2]
71: [38.5, 79, 17]
72: [38.5, 79, 32] 

### RE: VBA permutation of arrays

(OP)
Wow,

Thank you all for your responses

I'll try the VBA code suggested

(OP)
Thanks mikrom
It works great

### RE: VBA permutation of arrays

Hi Katto,

I'm glad to be able to help you a little bit.
It works, but now when I look at the code again, I see some places where it could be improved. I haven't done anything in VBA for a long time and so it wasn't that easy for me to rewrite the Python code in VBA at first. It was an opportunity for me to write something in VBA again and afterwards I had a good feeling that I had succeeded in the end.

### RE: VBA permutation of arrays

(OP)
it's a good start. eanough to allow me to cusotmize it to my specific need
thanks again

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!