ExcelBanter - Producing a list of all possible combinations

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Producing a list of all possible combinations (https://www.excelbanter.com/excel-programming/289503-producing-list-all-possible-combinations.html)

Producing a list of all possible combinations

Hello all,

Man, am I stuck. I need to write some VB code that will
take answers to a questionnaire and produce a spreadsheet
listing every possible combination of answers. For
example, there are three questions, each with three
answers: a, b, or c. I need the output to be:

a a a
b a a
c a a
a b a
a c a
a a b
etc...

Can anybody think of a way to make this happen?

I appreciate any and all help.

Thanks in advance and have a great one,

Mike

Producing a list of all possible combinations

Permutation is your keyword here.

Try this post by Leo Heuser

http://groups.google.co.nz/groups?se...&output=gplain

Rob

"Mike" wrote in message
...
Hello all,

Man, am I stuck. I need to write some VB code that will
take answers to a questionnaire and produce a spreadsheet
listing every possible combination of answers. For
example, there are three questions, each with three
answers: a, b, or c. I need the output to be:

a a a
b a a
c a a
a b a
a c a
a a b
etc...

Can anybody think of a way to make this happen?

I appreciate any and all help.

Thanks in advance and have a great one,

Mike

Producing a list of all possible combinations

mike,

don't have anything in VB, but if you know any javascript you could
try translating this:

tim

<html
<head
<titleCombinations</title

<script type="text/javascript"

// Given an array of arrays of various lengths,
// return an array of arrays where
// "numRows" = product of all array lengths
// "numCols" = length of original array parameter
// Assume all passed arrays hold only unique values
function GetCombinations(arr){

var arrReturn=new Array(); // returned array
var nProduct;
var repeats;
var tmp;

// find product of the array lengths
nProduct=1;
for(x=0;x<arr.length;x++)nProduct=nProduct*arr[x].length;

// initialise the final array
for(n=0;n<nProduct;n++) arrReturn[n]=new Array();

repeats=nProduct;

for(idx=0;idx<arr.length;idx++){

tmp=arr[idx];
repeats=repeats/tmp.length;
z=0;
//loop through each value in arr[idx], repeating it [repeat] times,
//do this nProduct/(arr[idx]*repeats) times
for(a=0;a<(nProduct/(tmp.length*repeats));a++){
for(n=0;n<tmp.length;n++){
for(m=1;m<=repeats;m++){
arrReturn[z][idx]=tmp[n];
z++;
}
}
}
}

return arrReturn;
}

</script

</head

<body

<script type="text/javascript"

var tmp;
var arr1=new Array("A","B");
var arr2=new Array("1","2","3");
var arr3=new Array("W","X","Y","Z");
var arr4=new Array("K","L","M","N","P");

var newArr=GetCombinations(new Array(arr1,arr2,arr3,arr4));

document.write("<h3" + newArr.length + "items: </h3");

for(x=0;x<newArr.length;x++){
document.write(newArr[x].join(" - ") + "<br");
}
</script
</body
</html

"Rob van Gelder" wrote in
message ...
Permutation is your keyword here.

Try this post by Leo Heuser

http://groups.google.co.nz/groups?se...&output=gplain

Rob

"Mike" wrote in message
...
Hello all,

Man, am I stuck. I need to write some VB code that will
take answers to a questionnaire and produce a spreadsheet
listing every possible combination of answers. For
example, there are three questions, each with three
answers: a, b, or c. I need the output to be:

a a a
b a a
c a a
a b a
a c a
a a b
etc...

Can anybody think of a way to make this happen?

I appreciate any and all help.

Thanks in advance and have a great one,

Mike