Hi, I am wondering if it is possible to write a vb script that outputs all
possible permutations (combinations) of two arrays of variables. For
instance, i have array1 and array2 in A1:A10 and B1:B10 of sheet1. I want to
output the 100 different combinations of variables from array1 and array2
into columns A and B of sheet2. I hope this makes sense. Let me know if you
have any ideas.

Thanks!

Do you want combinations or permutations?

I'm not sure how you want to distribute the 100 combinations among
columns A & B, but if you want the 100 combinations, with column A
first, to be in Sheet2!A1:A100, enter

A1: =INDEX(Sheet1!A:A,INT((ROW()-1)/10)+1) &
INDEX(Sheet1!B:B,MOD(ROW()-1,10)+1)

and copy down.


In article ,
Henrik wrote:

Hi, I am wondering if it is possible to write a vb script that outputs all
possible permutations (combinations) of two arrays of variables. For
instance, i have array1 and array2 in A1:A10 and B1:B10 of sheet1. I want to
output the 100 different combinations of variables from array1 and array2
into columns A and B of sheet2. I hope this makes sense. Let me know if you
have any ideas.

John's site:
http://www.j-walk.com/ss/excel/tips/tip46.htm

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"JE McGimpsey" wrote in message
...
Do you want combinations or permutations?

I'm not sure how you want to distribute the 100 combinations among
columns A & B, but if you want the 100 combinations, with column A
first, to be in Sheet2!A1:A100, enter

A1: =INDEX(Sheet1!A:A,INT((ROW()-1)/10)+1) &
INDEX(Sheet1!B:B,MOD(ROW()-1,10)+1)

and copy down.


In article ,
Henrik wrote:

Hi, I am wondering if it is possible to write a vb script that outputs
all
possible permutations (combinations) of two arrays of variables. For
instance, i have array1 and array2 in A1:A10 and B1:B10 of sheet1. I want
to
output the 100 different combinations of variables from array1 and array2
into columns A and B of sheet2. I hope this makes sense. Let me know if
you
have any ideas.

Hit the send key too fast. If you want to write a macro, you can use
something like:

Public Sub Combos()
Dim a As Variant
Dim b As Variant
Dim c As Variant
Dim i As Long
Dim j As Long
Dim nAs As Long
Dim nBs As Long
With Sheets("Sheet1")
a = .Range("A1:A10").Value
b = .Range("B1:B10").Value
End With
nAs = UBound(a, 1)
nBs = UBound(b, 1)
ReDim c(1 To nAs * nBs, 1 To 1)
For i = 1 To nAs
For j = 1 To nBs
c((i - 1) * nBs + j, 1) = a(i, 1) & b(j, 1)
Next j
Next i
Sheets("Sheet2").Range("A1").Resize(UBound(c, 1), 1).Value = c
End Sub

If you want to flip the A and B values and put them in column B, make a
minor change:

Public Sub Combos()
Dim a As Variant
Dim b As Variant
Dim c As Variant
Dim i As Long
Dim j As Long
Dim nAs As Long
Dim nBs As Long
With Sheets("Sheet1")
a = .Range("A1:A10").Value
b = .Range("B1:B10").Value
End With
nAs = UBound(a, 1)
nBs = UBound(b, 1)
ReDim c(1 To nAs * nBs, 1 To 2)
For i = 1 To nAs
For j = 1 To nBs
c((i - 1) * nBs + j, 1) = a(i, 1) & b(j, 1)
c((i - 1) * nBs + j, 2) = b(j, 1) & a(i, 1)
Next j
Next i
Sheets("Sheet2").Range("A1").Resize(UBound(c, 1), 2).Value = c
End Sub




In article ,
JE McGimpsey wrote:

Do you want combinations or permutations?

I'm not sure how you want to distribute the 100 combinations among
columns A & B, but if you want the 100 combinations, with column A
first, to be in Sheet2!A1:A100, enter

A1: =INDEX(Sheet1!A:A,INT((ROW()-1)/10)+1) &
INDEX(Sheet1!B:B,MOD(ROW()-1,10)+1)

and copy down.


In article ,
Henrik wrote:

Hi, I am wondering if it is possible to write a vb script that outputs all
possible permutations (combinations) of two arrays of variables. For
instance, i have array1 and array2 in A1:A10 and B1:B10 of sheet1. I want
to
output the 100 different combinations of variables from array1 and array2
into columns A and B of sheet2. I hope this makes sense. Let me know if you
have any ideas.