Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ??
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ??
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" wrote: 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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ??
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 ! ... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ??
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 ? ... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ?
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 "ytayta555" wrote: 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 ? ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ??
(..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" wrote in message ... 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 ! ... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ?
On 11 Iul, 04:58, Jim Skrydlak
wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ??
On 11 Iul, 05:55, "Dana DeLouis" wrote:
(..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, 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:F4 5)*{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 ! ... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ?
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" wrote: On 11 Iul, 05:55, "Dana DeLouis" wrote: (..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, 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:F4 5)*{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 ! ... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ?
On 11 Iul, 16:36, Jim Skrydlak
wrote: 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 ? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ?
Can someone to provide me the macro I need , please ?
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
I NEED macro for Combinations , Permutations or Arrangements ?
Can anybody here help me with more advices ?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a list of permutations and combinations? | Excel Discussion (Misc queries) | |||
How to create a macro in excel so that it can generate a list ofunique records using all permutations and combinations of the data in eachrow ad column | Excel Discussion (Misc queries) | |||
permutations/combinations | Excel Programming | |||
Permutations or Combinations or some other function?? | Excel Discussion (Misc queries) | |||
permutations/combinations | Excel Programming |