VBA to create new data help please
 

I have 4 different sets of datalists currently.

Dataset A, with values A1, A2, A3,....
Dataset B, with values B1, B2, B3,.....
Dataset C, with values C1, C2, C3,.....
Dataset D, with values D1, D2, D3,.....

What I need to do is 'merge' them all together to create a 4 column list
with each possible combination.

i.e.

A1,B1,C1,D1
A1,B2,C1,D2....
A1,B1,C2,D1
A1,B1,C2,D2.... etc

I may not have explained this very well and am not that good with VBA so any
help at all, appreciated please. Happy to try and explain further!
Many thanks

VBA to create new data help please
 

does this code:

Sub kombin()
Dim counter as Integer

For j =1 to Range("A1:A3").Cells.Count
For k =1 to Range("B1:B3").Cells.Count
For m =1 to Range("C1:C3").Cells.Count
For n =1 to Range("D1:D3").Cells.Count

counter = counter + 1

Cells(Counter+6,1)=Cells(1,j)
Cells(Counter+6,2)=Cells(1,k)
Cells(Counter+6,3)=Cells(1,m)
Cells(Counter+6,4)=Cells(1,n)


Next n
Next m
Next k
Next j

End Sub

do what you're up to?

adjust your ranges to suit


On 9 Kwi, 11:08, Andy wrote:
I have 4 different sets of datalists currently.

Dataset A, with values A1, A2, A3,....
Dataset B, with values B1, B2, B3,.....
Dataset C, with values C1, C2, C3,.....
Dataset D, with values D1, D2, D3,.....

What I need to do is 'merge' them all together to create a 4 column list
with each possible combination.

i.e.

A1,B1,C1,D1
A1,B2,C1,D2....
A1,B1,C2,D1
A1,B1,C2,D2.... etc

I may not have explained this very well and am not that good with VBA so any
help at all, appreciated please. Happy to try and explain further!
Many thanks

VBA to create new data help please
 

Hello Andy,

This is the program that combines a1,a2,a3 with b1,b2 amd b3 and c1,c2,c3
with d1,d2 and d3:


------------------------------------------------------------------

Sub kombin()
Dim counter As Integer
Range("a7:d87").Clear
For j = 1 To 3
For k = 1 To 3
For m = 1 To 3
For n = 1 To 3

counter = counter + 1
Cells(counter + 6, 1) = Cells(j, 1)
Cells(counter + 6, 2) = Cells(k, 2)
Cells(counter + 6, 3) = Cells(m, 3)
Cells(counter + 6, 4) = Cells(n, 4)
Next n
Next m

Next k
Next j
End Sub
-----------------------------------------------------------
My a1-a3 was 1,11,111
b1-b3 was 2,22,222
c1-c3 was 3,33,333
d1-d3 was 4,44,444
----------------------------------------------------------------
the output is:

1 2 3 4
1 2 3 44
1 2 3 444
1 2 33 4
1 2 33 44
1 2 33 444
1 2 333 4
1 2 333 44
1 2 333 444
1 22 3 4
1 22 3 44
1 22 3 444
1 22 33 4
1 22 33 44
1 22 33 444
1 22 333 4
1 22 333 44
1 22 333 444
1 222 3 4
1 222 3 44
1 222 3 444
1 222 33 4
1 222 33 44
1 222 33 444
1 222 333 4
1 222 333 44
1 222 333 444
11 2 3 4
11 2 3 44
11 2 3 444
11 2 33 4
11 2 33 44
11 2 33 444
11 2 333 4
11 2 333 44
11 2 333 444
11 22 3 4
11 22 3 44
11 22 3 444
11 22 33 4
11 22 33 44
11 22 33 444
11 22 333 4
11 22 333 44
11 22 333 444
11 222 3 4
11 222 3 44
11 222 3 444
11 222 33 4
11 222 33 44
11 222 33 444
11 222 333 4
11 222 333 44
11 222 333 444
111 2 3 4
111 2 3 44
111 2 3 444
111 2 33 4
111 2 33 44
111 2 33 444
111 2 333 4
111 2 333 44
111 2 333 444
111 22 3 4
111 22 3 44
111 22 3 444
111 22 33 4
111 22 33 44
111 22 33 444
111 22 333 4
111 22 333 44
111 22 333 444
111 222 3 4
111 222 3 44
111 222 3 444
111 222 33 4
111 222 33 44
111 222 33 444
111 222 333 4
111 222 333 44
111 222 333 444


----- Original Message -----
From: "Andy"
Newsgroups: microsoft.public.excel.programming
Sent: Friday, April 09, 2010 5:08 AM
Subject: VBA to create new data help please