VBA macro to create unique permutations from different columns

[dirty_pencil]

[FONT=&quot]Hi,[/FONT]
[FONT=&quot]I am trying to create a macro in VBA that takes data from three different columns and creates unique permutations while excluding any rows that have the same value in the first two columns. Example below;[/FONT]
[FONT=&quot]
[/FONT]
[TABLE="class: yklcuq-19 cUmOil"]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: yklcuq-20 dqbluc, bgcolor: rgba(255, 255, 255, 0.8)"]
[TH="class: yklcuq-25 kFFvu, align: center"]1[/TH]
[TH="class: yklcuq-25 kFFvu, align: center"]1[/TH]
[TH="class: yklcuq-25 kFFvu, align: center"]A[/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: yklcuq-20 dqbluc, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: yklcuq-21 gHVYpd"]2[/TD]
[TD="class: yklcuq-21 gHVYpd"]1[/TD]
[TD="class: yklcuq-21 gHVYpd"]B[/TD]
[/TR]
[TR="class: yklcuq-20 dqbluc, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: yklcuq-21 gHVYpd"]3[/TD]
[TD="class: yklcuq-21 gHVYpd"]1[/TD]
[TD="class: yklcuq-21 gHVYpd"]C[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]Unique permutations, excluding duplicate values in columns one and two would be[/FONT]
[FONT=&quot]2-1-A[/FONT]
[FONT=&quot]2-1-B[/FONT]
[FONT=&quot]2-1-C[/FONT]
[FONT=&quot]3-1-A[/FONT]
[FONT=&quot]3-1-B[/FONT]
[FONT=&quot]3-1-C[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]So permutations like 1-1-A would be exclude, since there is a 1 in both columns.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Here is the code that I have now that creates the permutations but doesn't exclude the 1-1-A type instances.

[/FONT]
[FONT=&quot]<code class="yklcuq-7 iRRQrr" style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-stretch: inherit; font-size: 13px; line-height: 20px; font-family: "Noto Mono", Menlo, Consolas, "Courier New", monospace; vertical-align: baseline; background: transparent; color: rgb(34, 34, 34); max-width: 100%; overflow: auto;">Sub

Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim out() As Variant
Dim j, k, l, m As Long


Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim out1 As Range


Set col1 = Range("A2", Range("A2").End(xlDown))
Set col2 = Range("B2", Range("B2").End(xlDown))
Set col3 = Range("C2", Range("C2").End(xlDown))

c1 = col1
c2 = col2
c3 = col3

Set out1 = Range("E2", Range("G2").Offset(UBound(c1) * UBound(c2) * UBound(c3)))
out = out1

j = 1
k = 1
l = 1
m = 1


Do While j <= UBound(c1)
Do While k <= UBound(c2)
Do While l <= UBound(c3)
out(m, 1) = c1(j, 1)
out(m, 2) = c2(k, 1)
out(m, 3) = c3(l, 1)
m = m + 1
l = l + 1
Loop
l = 1
k = k + 1
Loop
k = 1
j = j + 1
Loop


out1.Value = out
End Sub
</code>Any ideas?
[/FONT]

 

[Nine Zero]

Dim arow As Long, brow As Long, crow As Long, counter As Long

Yup that will do that trick, should have done Long to begin with, shouldn't have assumed data size

 

[dirty_pencil]

Awesome, thanks. What did that last part do?

 

[Nine Zero]

it is memory allocation
[TABLE="class: wikitable"]
<tbody>[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EAECF0]#EAECF0[/URL] , align: center"]Type[/TH]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EAECF0]#EAECF0[/URL] , align: center"]Storage[/TH]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EAECF0]#EAECF0[/URL] , align: center"]Range of Values[/TH]
[/TR]
[TR]
[TD]Byte[/TD]
[TD]1 byte[/TD]
[TD]0 to 255[/TD]
[/TR]
[TR]
[TD]Integer[/TD]
[TD]2 bytes[/TD]
[TD]-32,768 to 32,767[/TD]
[/TR]
[TR]
[TD]Long[/TD]
[TD]4 bytes[/TD]
[TD]-2,147,483,648 to 2,147,483,647[/TD]
[/TR]
</tbody>[/TABLE]