Permutations, Array, For...Next, etc.
 

I have two columns of data that have repeating values(columns B and D). Both
are the same length. I want to find all the permutations of this data,
specifically the values of column D minus column B.

What I have done so far is made an array of all of the differences that can
exist. My problem is I want to take a value from column 1 of the new array,
then a different value from column 2, etc. without duplicating any of the row
values. I have started to do this using For...next, however with 50 columns
and rows it will be a lot of code to type 50 for...next commands with 50
boolean statements to assure that the data doesn't repeat. Is there a better
way to do this or should I start typing ASAP?

Permutations, Array, For...Next, etc.
 

James,

What is it that you are trying to do? Finding all the permutations of 50 different numbers can
involve a lot of numbers - more that Excel can handle. Are you trying to find a specific sum,
perhaps?

HTH,
Bernie
MS Excel MVP


"James Michael" <James wrote in message
...
I have two columns of data that have repeating values(columns B and D). Both
are the same length. I want to find all the permutations of this data,
specifically the values of column D minus column B.

What I have done so far is made an array of all of the differences that can
exist. My problem is I want to take a value from column 1 of the new array,
then a different value from column 2, etc. without duplicating any of the row
values. I have started to do this using For...next, however with 50 columns
and rows it will be a lot of code to type 50 for...next commands with 50
boolean statements to assure that the data doesn't repeat. Is there a better
way to do this or should I start typing ASAP?

Permutations, Array, For...Next, etc.
 

Bernie,
I am trying to change the orientation of the values in each column so I have
the fewest different values in a third column. The third column would be the
values from column D minus column B. I have tried different methods and the
best one I have found is to make all possible tables and then select the best
from that. This is in VBA as well...

Thanks,
James

"Bernie Deitrick" wrote:

James,

What is it that you are trying to do? Finding all the permutations of 50 different numbers can
involve a lot of numbers - more that Excel can handle. Are you trying to find a specific sum,
perhaps?

HTH,
Bernie
MS Excel MVP


"James Michael" <James wrote in message
...
I have two columns of data that have repeating values(columns B and D). Both
are the same length. I want to find all the permutations of this data,
specifically the values of column D minus column B.

What I have done so far is made an array of all of the differences that can
exist. My problem is I want to take a value from column 1 of the new array,
then a different value from column 2, etc. without duplicating any of the row
values. I have started to do this using For...next, however with 50 columns
and rows it will be a lot of code to type 50 for...next commands with 50
boolean statements to assure that the data doesn't repeat. Is there a better
way to do this or should I start typing ASAP?

Permutations, Array, For...Next, etc.
 

James,

Try sorting both sets (Columns B and D) in the same manner, or matching pairs of equal numbers, then
close numbers, etc. Beyond that, I can't imagine that Excel will be able to handle this problem:
there are just too many ways to combine two sets of 50 numbers to be able to calculate the best
possible.

HTH,
Bernie
MS Excel MVP


"James Michael" wrote in message
...
Bernie,
I am trying to change the orientation of the values in each column so I have
the fewest different values in a third column. The third column would be the
values from column D minus column B. I have tried different methods and the
best one I have found is to make all possible tables and then select the best
from that. This is in VBA as well...

Thanks,
James

"Bernie Deitrick" wrote:

James,

What is it that you are trying to do? Finding all the permutations of 50 different numbers can
involve a lot of numbers - more that Excel can handle. Are you trying to find a specific sum,
perhaps?

HTH,
Bernie
MS Excel MVP


"James Michael" <James wrote in message
...
I have two columns of data that have repeating values(columns B and D). Both
are the same length. I want to find all the permutations of this data,
specifically the values of column D minus column B.

What I have done so far is made an array of all of the differences that can
exist. My problem is I want to take a value from column 1 of the new array,
then a different value from column 2, etc. without duplicating any of the row
values. I have started to do this using For...next, however with 50 columns
and rows it will be a lot of code to type 50 for...next commands with 50
boolean statements to assure that the data doesn't repeat. Is there a better
way to do this or should I start typing ASAP?