×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

VBA permutation of arrays

VBA permutation of arrays

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
 
ponder

---- 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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
           list_new.Add e
         Next e
         list_new.Add y
         product_of_two.Add list_new
       Else
         list_new.Add x
         list_new.Add y
         product_of_two.Add list_new
       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
    a.Add Item
  Next Item
  
  values = Array(10, 33, 56, 79)
  
  For Each Item In values
    b.Add Item
  Next Item
  
  values = Array(2, 17, 32)
  
  For Each Item In values
    c.Add Item
  Next Item
  
  values = Array(100, 200)
  
  For Each Item In values
    d.Add Item
  Next Item
  
  input_lists.Add a
  input_lists.Add b
  input_lists.Add c
  'input_lists.Add d
  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

RE: VBA permutation of arrays

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

Reply To This Thread

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close