I NEED macro for Combinations , Permutations or Arrangements ??

[ytayta555]

Hi , and a good day programmers !


I need strong something very strange and not too usual !! I have
this :

ex: 1

{1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0|0|0|0|0|0|0|0|0}

which is an array in a formula ; for a more clear look , I write down
simplified , (this ,only for
example) :

1 1 1 1 1 0 0 0 0 0
1 1 1 1 0 1 0 0 0 0
1 1 1 1 0 0 1 0 0 0
1 1 1 1 0 0 0 1 0 0
1 1 1 1 0 0 0 0 1 0
1 1 1 1 0 0 0 0 0 1
1 1 1 0 1 1 0 0 0 0
1 1 1 0 1 0 1 0 0 0
1 1 1 0 1 0 0 1 0 0
etc .

I have like in first example , 45 of numbers , 15 of 1 and 30 of 0 ;
I don't know what kind of VBA macro I must use : macro for return
combinations , or permutations , or arrangements ! !
....because , when number 1 must move at every combinations (or
arrangements ) of 45 numbers/variants , in it's place must replace
with 0 ;
_________________________________________________________________
In the 45 cells with which macro shall work , the value in every cell
shall look so :

|0 or 1| ; I don't know how them must declare !!
Dim i1 As Long ..... I don't know if it works , because maybe this
kind of values
aren't recognise like a number value !!

Any sugestions will be helpfull
It's a great thing for me to know and only what kind of macro I must
try : combine ,
permutations or arrangements !

 

[Jim Skrydlak]

Assuming that the array starts in cell A1, something that will give you
everything from 0 0 0 0 0 0 0 0 0 0 to 1 1 1 1 1 1 1 1 1 1 is:

Sub younameit()

Dim number As Double

Dim col As Integer
Dim ro As Integer

For ro = 1 To 1024 ' 1024 is 2^10
number = ro - 1
For col = 1 To 10
ActiveSheet.Cells(ro, 11 - col) = number Mod 2
number = Int(number / 2)
Next col
Next ro
End Sub

Hope this helps. Best of luck.

Jim

 

[ytayta555]

WAW ; here is like in a dream world

It works , but how can I adjust for my needs ?
I need to have in every variant 15 cells with 1 , ant the others in
one row to be 0 ; and ...
and to have 30 of o and 15 of 1 in every row (combination ) !
For my needs I must have 45 in total .
And , not to stop to 1024 , the macro to do the combin untill the
combinations are to ends
(..I know that is a very enormous numbers of combinations )

Thanks so much for the way you gived me ! ...

 

[ytayta555]

In every row and 45 columns (which is a combination ), I have need
to be 15 ! of 1 , ant the other 30 of 0 ? How can I get
them ? ...

 

[Jim Skrydlak]

I think that this works. It's a bit inelegant, but it seems to give the
right answer.

Once again, good luck!

Jim

Option Explicit
Sub younameit()

Dim col As Integer
Dim ro As Integer

For col = 1 To 5
ActiveSheet.Cells(1, col) = 1
ActiveSheet.Cells(1, col + 5) = 0
Next col

For ro = 2 To 45
For col = 1 To 5
If ro Mod (6 * 2 ^ (5 - col)) = 1 Then
ActiveSheet.Cells(ro, col) = 1 - ActiveSheet.Cells(ro - 1, col)
Else
ActiveSheet.Cells(ro, col) = ActiveSheet.Cells(ro - 1, col)
End If

If ro = 2 Then ActiveSheet.Cells(ro, 5) = 0

Next col
For col = 6 To 10
If col = 5 + (ro Mod 5) Then
ActiveSheet.Cells(ro, col) = 1
Else
ActiveSheet.Cells(ro, col) = 0
End If
If (ro Mod 5) = 0 Then ActiveSheet.Cells(ro, 10) = 1
Next col
Next ro
End Sub

 

[Dana DeLouis]

(..I know that is a very enormous numbers of combinations )

Yes. I believe there are this many unique permutations of your data:

=COMBIN(15+30,15)

344,867,425,584

(Are you sure you want to list them all?)
--
HTH :>)
Dana DeLouis

 

[ytayta555]

I think that this works.  It's a bit inelegant, but it seems to give the

A good day !

It isn't working still !
With the last macro above , is well first 6 rows ! In row 7 , are
6 of 1 ,
what is wrong for me !! in rows 13 :18 are 4 of 1 what it isn't the
solution
for my needs ! ...

In *EVERY* combination (or permutation, I don't know what it
is ...),
I need to have *15* of 1 , no one more or less !

The macro put results in 10 Columns , and I need in *45* Columns ! :
*15* of 1 , and *30* of 0 .

Thanks so much for your time

 

[ytayta555]

Yes.  I believe there are this many unique permutations of your data:
=COMBIN(15+30,15)
 344,867,425,584,

At last , they are combinations or permutations what I need , and I
try to built ? Or arrangements ?

(Are you sure you want to list them all?)

I need only some few 40.000.000
___________________________________________
This is a formula :
=AND(MMULT(TRANSPOSE(ROW(B1:F45))^0;ISNUMBER(B1:F45)*{1|0|1|0|1|1|0|1|
0|1|1|0|1|0|1|1|0|1|0|1|1|0|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0})<=1)

Generating another arrangements in this array I generate references in
combinatoric order ,
that't why I need the macro which can do that ! ...

 

[Jim Skrydlak]

I finally understand what you're trying to do, thanks to Dana. The only way
that I can think of to do it is to create the binary representations of
numbers from 0 to 2^45-1. Unfortunately, a long integer number is only
2^31-1. Moreover, generating any significant number of those would take an
awfully long time.

A few thoughts:

You can pretty much do your own binary arithmetic by creating a 45-character
long string. Start with a string of fifteen zeros followed by thirty ones;
that's the smallest binary number containing thirty ones. Then simulate
addition by checking the right-most character; if it's one, turn it into a
zero, and carry the one to the next character to the left; continue until you
don't encounter any ones that are already there.

Any time you create a string with fifteen zeros and thirty ones, its mirror
image will also contain fifteen zeros and thirty ones. By mirror image, I
mean a string in which the forty-fifth element is the first element of the
original string, the forty-fourth is the second element of the origina
string, etc.

Any time you create a string with fifteen ones and thirty zeros, its inverse
will contain fifteen zeros and thirty ones. Just change all the ones to
zeros and all the zeros to one. You'll then have to check whether you have
that string.

Once again, good luck!

And thank you to Dana for making this clear to me.

Jim

 

[ytayta555]

I finally understand what you're trying to do, thanks to Dana.  The only way
that I can think of to do it is to create the binary representations of
numbers from 0 to 2^45-1.  Unfortunately, a long integer number is only
2^31-1.  Moreover, generating any significant number of those would take an
awfully long time.

Can enybody to help me with a macro in this way , please ?

 

[ytayta555]

Can someone to provide me the macro I need , please ?

 

[ytayta555]

Can anybody here help me with more advices ?