Generate data with all combinations

[Venkatesh V]

HI,

Please provide help on how to generate data on all comibinations
in an excel I have 3 clumns
Sec - has 4 values
Acc - has 8 values
Type - 4 values

with the available data, i reuqire either an addin or macro or any tiool
that would generate data will all the combinations of values

so, with the above example - excel shoud have: 4 * 8* 4 =128 rows in excel.

how to do it?

Thanks,
Venkat

 

[Per Jessen]

This should do it. Just change columns as desired.

Const SecCol As String = "A"
Const AccCol As String = "B"
Const TypeCol As String = "C"
Const TargetCol As String = "E"
Const FirstRow As Integer = 2 ' Headings in row 1

Sub Combinations()
secCount = Range(Cells(FirstRow, SecCol), Cells(FirstRow,
SecCol).End(xlDown)).Rows.Count
AccCount = Range(Cells(FirstRow, AccCol), Cells(FirstRow,
AccCol).End(xlDown)).Rows.Count
TypeCount = Range(Cells(FirstRow, AccCol), Cells(FirstRow,
TypeCol).End(xlDown)).Rows.Count

For rSec = 1 To secCount
SecVal = Cells(rSec + 1, SecCol).Value
For rAcc = 1 To AccCount
AccVal = Cells(rAcc + 1, AccCol).Value
For rType = 1 To TypeCount
TypeVal = Cells(rType + 1, TypeCol).Value
Combination = SecVal & " " & AccVal & " " & TypeVal
Range(TargetCol & FirstRow).Offset(off, 0) = Combination
off = off + 1
Next
Next
Next
End Sub

Regards,
Per

 

[Venkatesh V]

HI Per Jessen,

Thanks i have done it like this. its returnign a runtime error '13' - type
mismatch
Please clarify.

I also wanted to know, instead of concatenation -- if it can put into
differnt cells;
forex:
combinaton of A2 B2 C2 in sheet2 -- row2
combinaton of A2 B3 C2 in sheet2 -- row3
combinaton of A2 B4 C2 in sheet2 -- row4
combinaton of A2 B5 C2 in sheet2 -- row5
combinaton of A2 B2 C3 in sheet2 -- row6
combinaton of A2 B2 C4 in sheet2 -- row7
....
....


Const SecCol As String = "B"
Const AccCol As String = "A"
Const TypeCol As String = "C"
Const TargetCol As String = "E"
Const FirstRow As Integer = 2 ' Headings in row 1

Sub Combinations()

secCount = Range(Cells("B2:B8", SecCol), Cells("B2:B8",
SecCol).End(xlDown)).Rows.Count
AccCount = Range(Cells("A2:A5", AccCol), Cells("A2:A5",
AccCol).End(xlDown)).Rows.Count
TypeCount = Range(Cells("C2:C5", AccCol), Cells("C2:C5",
TypeCol).End(xlDown)).Rows.Count

For rSec = 1 To secCount
SecVal = Cells(rSec + 1, SecCol).Value
For rAcc = 1 To AccCount
AccVal = Cells(rAcc + 1, AccCol).Value
For rType = 1 To TypeCount
TypeVal = Cells(rType + 1, TypeCol).Value
Combination = SecVal & " " & AccVal & " " & TypeVal
Range(TargetCol & FirstRow).Offset(off, 0) = Combination
off = off + 1
Next
Next
Next
End Sub

Thanks,
Venkat

 

[Gary''s Student]

Sub Venkatesh()
sec = Array("sec1", "sec2", "sec3", "sec4")
acc = Array(1, 2, 3, 4, 5, 6, 7, 8)
tipe = Array("t1", "t2", "t3", "t4")
L = 1
For i = 0 To 3
For j = 0 To 7
For k = 0 To 3
Cells(L, 1).Value = sec(i)
Cells(L, 2).Value = acc(j)
Cells(L, 3).Value = tipe(k)
L = L + 1
Next
Next
Next
End Sub

Naturally, put your own values in the array statements.

 

[Per Jessen]

Hi Venkat

Try this:

Const SecCol As String = "B"
Const AccCol As String = "A"
Const TypeCol As String = "C"
Const TargetCol As String = "A" ' Output column
Const FirstRow As Integer = 2 ' Headings in row 1

Sub Combinations()
Set TargetSH = Sheets("Sheet2")
secCount = Range("B2:B8").Rows.Count
AccCount = Range("A2:A5").Rows.Count
TypeCount = Range("C2:C5").Rows.Count

For rSec = 1 To secCount
SecVal = Cells(rSec + 1, SecCol).Value
For rAcc = 1 To AccCount
AccVal = Cells(rAcc + 1, AccCol).Value
For rType = 1 To TypeCount
TypeVal = Cells(rType + 1, TypeCol).Value
Combination = SecVal & " " & AccVal & " " & TypeVal
TargetSH.Range(TargetCol & FirstRow).Offset(off, 0) =
Combination
off = off + 1
Next
Next
Next
End Sub

Regards,
Per

 

[Venkatesh V]

excellent. thanks

Sub Venkatesh()
sec = Array("sec1", "sec2", "sec3", "sec4")
acc = Array(1, 2, 3, 4, 5, 6, 7, 8)
tipe = Array("t1", "t2", "t3", "t4")
L = 1
For i = 0 To 3
For j = 0 To 7
For k = 0 To 3
Cells(L, 1).Value = sec(i)
Cells(L, 2).Value = acc(j)
Cells(L, 3).Value = tipe(k)
L = L + 1
Next
Next
Next
End Sub

Naturally, put your own values in the array statements.