Vectors of all Permutation

[ThomasSchmidt]

Dear all,

i'm new to this forum, please excuse if i haven't figured out all the necessary details how to post a proper question including tables etc...

I am looking for a way in VBA to get a tabe populated with all conceivable permutations of a binary variable (think of an indicator or something like that). My starting tables look more or less like this:

T____P_1:_____P_2:_____P_3:_____P_4:_____P_5:_____P_6:_____P_7:_____P_8:
1
2
3
...
...

Say my vector-length is t (here T=3), then i have 2^3 =8 vectors containing all possible combinations. What i'm looking for is an algorithm that produces something like this below:

T____P_1:_____P_2:_____P_3:_____P_4:_____P_5:_____P_6:_____P_7:_____P_8:
1____|1|_______|1|_______|1|_______|1|_______|0|_______|0|_______|0|_______|0|
2____|1|_______|0|_______|1|_______|0|_______|1|_______|0|_______|1|_______|0|
3____|1|_______|1|_______|0|_______|0|_______|1|_______|1|_______|0|_______|0|

Needless to say that i have 1200 tables with various T (each table has a specific T-length) but across those tabes T can vary (thus a nested For..loop doesn't work for me i'm afraid) for which i have to perform this on a daily basis, so yes: VBA is obviously the way to do it. I have a working knowledge of VBA (basics) but somehow i'm too blind to see the obvious logic how to populate those tables...

Thank you so much for any help or tips!!

Thomas

 

[JGordon11]

BinomialPermutations.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	3
2	0	0	0	0	1	1	1	1
3	0	0	1	1	0	0	1	1
4	0	1	0	1	0	1	0	1
5
6	4
7	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1
8	0	0	0	0	1	1	1	1	0	0	0	0	1	1	1	1
9	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1	1
10	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1
11
12	5
13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
14	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1
15	0	0	0	0	1	1	1	1	0	0	0	0	1	1	1	1	0	0	0	0	1	1	1	1	0	0	0	0	1	1	1	1
16	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1	1
17	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1
Sheet1

Cell Formulas
Range	Formula
A2:H4	A2	=IFERROR(MID(DEC2BIN((COLUMN()-COLUMN($A$1)),$A$1),ROW()-ROW($A$1),1),"")
A7:P10	A7	=IFERROR(MID(DEC2BIN((COLUMN()-COLUMN($A$6)),$A$6),ROW()-ROW($A$6),1),"")
A13:AF17	A13	=IFERROR(MID(DEC2BIN((COLUMN()-COLUMN($A$12)),$A$12),ROW()-ROW($A$12),1),"")

 

[lrobbo314]

A way using VBA.

New__Document (60).xlsx
	A	B	C	D	E	F	G	H
1	0	0	0	0	0	0	0	0
2	0	0	0	0	0	0	0	1
3	0	0	0	0	0	0	1	0
4	0	0	0	0	0	0	1	1
5	0	0	0	0	0	1	0	0
6	0	0	0	0	0	1	0	1
7	0	0	0	0	0	1	1	0
8	0	0	0	0	0	1	1	1
9	0	0	0	0	1	0	0	0
10	0	0	0	0	1	0	0	1
11	0	0	0	0	1	0	1	0
12	0	0	0	0	1	0	1	1
13	0	0	0	0	1	1	0	0
14	0	0	0	0	1	1	0	1
15	0	0	0	0	1	1	1	0
16	0	0	0	0	1	1	1	1
17	0	0	0	1	0	0	0	0
18	0	0	0	1	0	0	0	1
19	0	0	0	1	0	0	1	0
20	0	0	0	1	0	0	1	1
21	0	0	0	1	0	1	0	0
22	0	0	0	1	0	1	0	1
23	0	0	0	1	0	1	1	0
24	0	0	0	1	0	1	1	1
25	0	0	0	1	1	0	0	0
26	0	0	0	1	1	0	0	1
27	0	0	0	1	1	0	1	0
28	0	0	0	1	1	0	1	1
29	0	0	0	1	1	1	0	0
30	0	0	0	1	1	1	0	1
31	0	0	0	1	1	1	1	0
32	0	0	0	1	1	1	1	1
33	0	0	1	0	0	0	0	0
34	0	0	1	0	0	0	0	1
35	0	0	1	0	0	0	1	0
36	0	0	1	0	0	0	1	1
37	0	0	1	0	0	1	0	0
38	0	0	1	0	0	1	0	1
39	0	0	1	0	0	1	1	0
40	0	0	1	0	0	1	1	1
41	0	0	1	0	1	0	0	0
42	0	0	1	0	1	0	0	1
43	0	0	1	0	1	0	1	0
44	0	0	1	0	1	0	1	1
45	0	0	1	0	1	1	0	0
46	0	0	1	0	1	1	0	1
47	0	0	1	0	1	1	1	0
48	0	0	1	0	1	1	1	1
49	0	0	1	1	0	0	0	0
50	0	0	1	1	0	0	0	1
51	0	0	1	1	0	0	1	0
52	0	0	1	1	0	0	1	1
53	0	0	1	1	0	1	0	0
54	0	0	1	1	0	1	0	1
55	0	0	1	1	0	1	1	0
56	0	0	1	1	0	1	1	1
57	0	0	1	1	1	0	0	0
58	0	0	1	1	1	0	0	1
59	0	0	1	1	1	0	1	0
60	0	0	1	1	1	0	1	1
61	0	0	1	1	1	1	0	0
62	0	0	1	1	1	1	0	1
63	0	0	1	1	1	1	1	0
64	0	0	1	1	1	1	1	1
65	0	1	0	0	0	0	0	0
66	0	1	0	0	0	0	0	1
67	0	1	0	0	0	0	1	0
68	0	1	0	0	0	0	1	1
69	0	1	0	0	0	1	0	0
70	0	1	0	0	0	1	0	1
71	0	1	0	0	0	1	1	0
72	0	1	0	0	0	1	1	1
73	0	1	0	0	1	0	0	0
74	0	1	0	0	1	0	0	1
75	0	1	0	0	1	0	1	0
76	0	1	0	0	1	0	1	1
77	0	1	0	0	1	1	0	0
78	0	1	0	0	1	1	0	1
79	0	1	0	0	1	1	1	0
80	0	1	0	0	1	1	1	1
81	0	1	0	1	0	0	0	0
82	0	1	0	1	0	0	0	1
83	0	1	0	1	0	0	1	0
84	0	1	0	1	0	0	1	1
85	0	1	0	1	0	1	0	0
86	0	1	0	1	0	1	0	1
87	0	1	0	1	0	1	1	0
88	0	1	0	1	0	1	1	1
89	0	1	0	1	1	0	0	0
90	0	1	0	1	1	0	0	1
91	0	1	0	1	1	0	1	0
92	0	1	0	1	1	0	1	1
93	0	1	0	1	1	1	0	0
94	0	1	0	1	1	1	0	1
95	0	1	0	1	1	1	1	0
96	0	1	0	1	1	1	1	1
97	0	1	1	0	0	0	0	0
98	0	1	1	0	0	0	0	1
99	0	1	1	0	0	0	1	0
100	0	1	1	0	0	0	1	1
101	0	1	1	0	0	1	0	0
102	0	1	1	0	0	1	0	1
103	0	1	1	0	0	1	1	0
104	0	1	1	0	0	1	1	1
105	0	1	1	0	1	0	0	0
106	0	1	1	0	1	0	0	1
107	0	1	1	0	1	0	1	0
108	0	1	1	0	1	0	1	1
109	0	1	1	0	1	1	0	0
110	0	1	1	0	1	1	0	1
111	0	1	1	0	1	1	1	0
112	0	1	1	0	1	1	1	1
113	0	1	1	1	0	0	0	0
114	0	1	1	1	0	0	0	1
115	0	1	1	1	0	0	1	0
116	0	1	1	1	0	0	1	1
117	0	1	1	1	0	1	0	0
118	0	1	1	1	0	1	0	1
119	0	1	1	1	0	1	1	0
120	0	1	1	1	0	1	1	1
121	0	1	1	1	1	0	0	0
122	0	1	1	1	1	0	0	1
123	0	1	1	1	1	0	1	0
124	0	1	1	1	1	0	1	1
125	0	1	1	1	1	1	0	0
126	0	1	1	1	1	1	0	1
127	0	1	1	1	1	1	1	0
128	0	1	1	1	1	1	1	1
129	1	0	0	0	0	0	0	0
130	1	0	0	0	0	0	0	1
131	1	0	0	0	0	0	1	0
132	1	0	0	0	0	0	1	1
133	1	0	0	0	0	1	0	0
134	1	0	0	0	0	1	0	1
135	1	0	0	0	0	1	1	0
136	1	0	0	0	0	1	1	1
137	1	0	0	0	1	0	0	0
138	1	0	0	0	1	0	0	1
139	1	0	0	0	1	0	1	0
140	1	0	0	0	1	0	1	1
141	1	0	0	0	1	1	0	0
142	1	0	0	0	1	1	0	1
143	1	0	0	0	1	1	1	0
144	1	0	0	0	1	1	1	1
145	1	0	0	1	0	0	0	0
146	1	0	0	1	0	0	0	1
147	1	0	0	1	0	0	1	0
148	1	0	0	1	0	0	1	1
149	1	0	0	1	0	1	0	0
150	1	0	0	1	0	1	0	1
151	1	0	0	1	0	1	1	0
152	1	0	0	1	0	1	1	1
153	1	0	0	1	1	0	0	0
154	1	0	0	1	1	0	0	1
155	1	0	0	1	1	0	1	0
156	1	0	0	1	1	0	1	1
157	1	0	0	1	1	1	0	0
158	1	0	0	1	1	1	0	1
159	1	0	0	1	1	1	1	0
160	1	0	0	1	1	1	1	1
161	1	0	1	0	0	0	0	0
162	1	0	1	0	0	0	0	1
163	1	0	1	0	0	0	1	0
164	1	0	1	0	0	0	1	1
165	1	0	1	0	0	1	0	0
166	1	0	1	0	0	1	0	1
167	1	0	1	0	0	1	1	0
168	1	0	1	0	0	1	1	1
169	1	0	1	0	1	0	0	0
170	1	0	1	0	1	0	0	1
171	1	0	1	0	1	0	1	0
172	1	0	1	0	1	0	1	1
173	1	0	1	0	1	1	0	0
174	1	0	1	0	1	1	0	1
175	1	0	1	0	1	1	1	0
176	1	0	1	0	1	1	1	1
177	1	0	1	1	0	0	0	0
178	1	0	1	1	0	0	0	1
179	1	0	1	1	0	0	1	0
180	1	0	1	1	0	0	1	1
181	1	0	1	1	0	1	0	0
182	1	0	1	1	0	1	0	1
183	1	0	1	1	0	1	1	0
184	1	0	1	1	0	1	1	1
185	1	0	1	1	1	0	0	0
186	1	0	1	1	1	0	0	1
187	1	0	1	1	1	0	1	0
188	1	0	1	1	1	0	1	1
189	1	0	1	1	1	1	0	0
190	1	0	1	1	1	1	0	1
191	1	0	1	1	1	1	1	0
192	1	0	1	1	1	1	1	1
193	1	1	0	0	0	0	0	0
194	1	1	0	0	0	0	0	1
195	1	1	0	0	0	0	1	0
196	1	1	0	0	0	0	1	1
197	1	1	0	0	0	1	0	0
198	1	1	0	0	0	1	0	1
199	1	1	0	0	0	1	1	0
200	1	1	0	0	0	1	1	1
201	1	1	0	0	1	0	0	0
202	1	1	0	0	1	0	0	1
203	1	1	0	0	1	0	1	0
204	1	1	0	0	1	0	1	1
205	1	1	0	0	1	1	0	0
206	1	1	0	0	1	1	0	1
207	1	1	0	0	1	1	1	0
208	1	1	0	0	1	1	1	1
209	1	1	0	1	0	0	0	0
210	1	1	0	1	0	0	0	1
211	1	1	0	1	0	0	1	0
212	1	1	0	1	0	0	1	1
213	1	1	0	1	0	1	0	0
214	1	1	0	1	0	1	0	1
215	1	1	0	1	0	1	1	0
216	1	1	0	1	0	1	1	1
217	1	1	0	1	1	0	0	0
218	1	1	0	1	1	0	0	1
219	1	1	0	1	1	0	1	0
220	1	1	0	1	1	0	1	1
221	1	1	0	1	1	1	0	0
222	1	1	0	1	1	1	0	1
223	1	1	0	1	1	1	1	0
224	1	1	0	1	1	1	1	1
225	1	1	1	0	0	0	0	0
226	1	1	1	0	0	0	0	1
227	1	1	1	0	0	0	1	0
228	1	1	1	0	0	0	1	1
229	1	1	1	0	0	1	0	0
230	1	1	1	0	0	1	0	1
231	1	1	1	0	0	1	1	0
232	1	1	1	0	0	1	1	1
233	1	1	1	0	1	0	0	0
234	1	1	1	0	1	0	0	1
235	1	1	1	0	1	0	1	0
236	1	1	1	0	1	0	1	1
237	1	1	1	0	1	1	0	0
238	1	1	1	0	1	1	0	1
239	1	1	1	0	1	1	1	0
240	1	1	1	0	1	1	1	1
241	1	1	1	1	0	0	0	0
242	1	1	1	1	0	0	0	1
243	1	1	1	1	0	0	1	0
244	1	1	1	1	0	0	1	1
245	1	1	1	1	0	1	0	0
246	1	1	1	1	0	1	0	1
247	1	1	1	1	0	1	1	0
248	1	1	1	1	0	1	1	1
249	1	1	1	1	1	0	0	0
250	1	1	1	1	1	0	0	1
251	1	1	1	1	1	0	1	0
252	1	1	1	1	1	0	1	1
253	1	1	1	1	1	1	0	0
254	1	1	1	1	1	1	0	1
255	1	1	1	1	1	1	1	0
256	1	1	1	1	1	1	1	1
Sheet2

Sub MP()
Dim vLEN As Integer:    vLEN = 3
Dim POW As Integer:     POW = 2 ^ vLEN
Dim AR() As Variant:    AR = Evaluate("TRANSPOSE(ROW(1:" & POW & ")/ROW(1:" & POW & "))")
Dim TMP As Variant:     ReDim TMP(1 To POW)
Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")

Perm AR, TMP, 1, AL

With Range("A1").Resize(AL.Count, 1)
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, Space:=True
End With

End Sub

Sub Perm(Org As Variant, TMP As Variant, lInd As Long, AL As Object)
Dim Total As Double

For i = 0 To Org(lInd)
    TMP(lInd) = i
    If lInd = UBound(Org) Then
        AL.Add Join(TMP)
    Else
        Perm Org, TMP, lInd + 1, AL
    End If
Next i
End Sub

 

[lrobbo314]

Never mind. Mine does all of them. I misunderstood the OP.

 

[ThomasSchmidt]

Hi Irobot314, thanks a lot!

I changed your suggested code a bit such i have it in the form that i need. I also like your idea to put the vectors in AL. I guess taging a number on it and store it as a string saves space - in particular if T grows pretty large such that excel cannot handle it anymore...

Thanks also to JGordon11- your trick helps to validate the results

Thank you both again for your help!
Thomas