| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Every 5 digit combination
As a newby to Excel VBA, I'd like to create a spreadsheet that would
give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
The free Excel add-in "Display Word Permutations" provided
the following list. (the list is created in a single column) Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA 13579 35179 57139 79135 13597 35197 57193 79153 13759 35719 57319 79315 13795 35791 57391 79351 13957 35917 57913 79513 13975 35971 57931 79531 15379 37159 59137 91357 15397 37195 59173 91375 15739 37519 59317 91537 15793 37591 59371 91573 15937 37915 59713 91735 15973 37951 59731 91753 17359 39157 71359 93157 17395 39175 71395 93175 17539 39517 71539 93517 17593 39571 71593 93571 17935 39715 71935 93715 17953 39751 71953 93751 19357 51379 73159 95137 19375 51397 73195 95173 19537 51739 73519 95317 19573 51793 73591 95371 19735 51937 73915 95713 19753 51973 73951 95731 31579 53179 75139 97135 31597 53197 75193 97153 31759 53719 75319 97315 31795 53791 75391 97351 31957 53917 75913 97513 31975 53971 75931 97531 '----------------------- "seagee69" wrote in message As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
I believe this will do what you want - allows entries like 11111 as well as
12345, plus you can easily see how the 'move' to next row is made. There are two ways to move around on an Excel sheet - 'physically' by using a statement similar to: ActiveCell.Offset(1,0).Activate which says move down 1 row from where ever the current active cell is. But if you set up a variable to use as a pointer or row counter, you can move 'logically' using it - the ActiveCell remains in one location and you work in cells relative to it as RowOffset=23 Range("A1").Select ActiveCell.Offset(RowOffset,0) = "23 Rows Below the Active Cell" Use RowOffset of 0 to put something on the same row with the ActiveCell. Using the logical movement is much faster than actually physically moving from cell to cell using the .Activate method. Sub NumericCombinations() Dim NumberSet(1 To 5) As Integer Dim RowOffset As Long ' pointer to rows Dim LC1 As Integer ' LoopCounter Dim LC2 As Integer ' LoopCounter Dim LC3 As Integer ' LoopCounter Dim LC4 As Integer ' LoopCounter Dim LC5 As Integer ' LoopCounter 'load the numbers into the array 'works for this special case of odd numbers For LC1 = 0 To 4 NumberSet(LC1 + 1) = (LC1 * 2) + 1 Next ' 'RowOffset 'moves' you down thru the rows logically 'not an actual physical move - faster this way RowOffset = 0 Range("A1").Select ' got to A1 on current active sheet For LC1 = LBound(NumberSet) To UBound(NumberSet) For LC2 = LBound(NumberSet) To UBound(NumberSet) For LC3 = LBound(NumberSet) To UBound(NumberSet) For LC4 = LBound(NumberSet) To UBound(NumberSet) For LC5 = LBound(NumberSet) To UBound(NumberSet) 'put each number in a separate cell ActiveCell.Offset(RowOffset, 0) = NumberSet(LC1) ActiveCell.Offset(RowOffset, 1) = NumberSet(LC2) ActiveCell.Offset(RowOffset, 2) = NumberSet(LC3) ActiveCell.Offset(RowOffset, 3) = NumberSet(LC4) ActiveCell.Offset(RowOffset, 4) = NumberSet(LC5) 'update pointer to logically move to next row: RowOffset = RowOffset + 1 'to actually physically move to next row: 'Activecell.Offset(1,0).activate 'if you use this method, then the number entry 'above be done by substituting 0, for RowOffset, in 'each of the instructions placing a value in the cells 'example: 'ActiveCell.Offset(0, 0) = NumberSet(LC1) ' Next ' LC5 Next ' LC4 Next ' LC3 Next ' LC2 Next ' LC1 End Sub "seagee69" wrote: As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
Recursive Permutations of all numbers with repeated numbers.
Put the numbers in the array in Main and run Main. Sub Main() Dim rw As Long Dim s As String, i As Long Dim v As Variant, num As Long Dim v1 As Variant v = Array(1, 3, 5, 7, 9) num = UBound(v) - LBound(v) + 1 If num 6 Then MsgBox "Won't fit in one column" Exit Sub End If ReDim v1(1 To num ^ num, 1 To 1) rw = 1 s = "" For i = LBound(v) To UBound(v) s = v(i) AA rw, s, v, v1 Next Range("A1").Resize(UBound(v1, 1), 1).Value = v1 End Sub Sub AA(rw As Long, s As String, v As Variant, v1 As Variant) Dim i As Long, l As Long For i = LBound(v) To UBound(v) s = s & "," & v(i) l = Len(s) - Len(Replace(s, ",", "")) + 1 If l = (UBound(v) - LBound(v) + 1) Then v1(rw, 1) = s rw = rw + 1 Else AA rw, s, v, v1 End If s = Left(s, Len(s) - (Len(v(i)) + 1)) Next End Sub -- Regards, Tom Ogilvy "seagee69" wrote in message oups.com... As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
Jim, just a head up. may not fill the bill so to speak.
The numbers can repeat Your doing straight permutations which don't allow numbers to repeat. straight permutations: 5! = 120 permutations with numbers repeating: 5^5 = 3125 -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... The free Excel add-in "Display Word Permutations" provided the following list. (the list is created in a single column) Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA 13579 35179 57139 79135 13597 35197 57193 79153 13759 35719 57319 79315 13795 35791 57391 79351 13957 35917 57913 79513 13975 35971 57931 79531 15379 37159 59137 91357 15397 37195 59173 91375 15739 37519 59317 91537 15793 37591 59371 91573 15937 37915 59713 91735 15973 37951 59731 91753 17359 39157 71359 93157 17395 39175 71395 93175 17539 39517 71539 93517 17593 39571 71593 93571 17935 39715 71935 93715 17953 39751 71953 93751 19357 51379 73159 95137 19375 51397 73195 95173 19537 51739 73519 95317 19573 51793 73591 95371 19735 51937 73915 95713 19753 51973 73951 95731 31579 53179 75139 97135 31597 53197 75193 97153 31759 53719 75319 97315 31795 53791 75391 97351 31957 53917 75913 97513 31975 53971 75931 97531 '----------------------- "seagee69" wrote in message As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
Tom,
Thanks for that, point taken. In fact the program removes duplicates when they do occur. Entries with duplicate characters have the results fed thru a collection to remove repeats. So, for instance, "food" produces this list of 12 items not 24... -- Regards, Jim Cone San Francisco, USA food dfoo dofo doof fdoo fodo odfo odof ofdo ofod oodf oofd '-------- "Tom Ogilvy" wrote in message Jim, just a head up. may not fill the bill so to speak. The numbers can repeat Your doing straight permutations which don't allow numbers to repeat. straight permutations: 5! = 120 permutations with numbers repeating: 5^5 = 3125 -- Regards, Tom Ogilvy "Jim Cone" wrote in message The free Excel add-in "Display Word Permutations" provided the following list. (the list is created in a single column) Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA 13579 35179 57139 79135 13597 35197 57193 79153 13759 35719 57319 79315 13795 35791 57391 79351 13957 35917 57913 79513 13975 35971 57931 79531 15379 37159 59137 91357 15397 37195 59173 91375 15739 37519 59317 91537 15793 37591 59371 91573 15937 37915 59713 91735 15973 37951 59731 91753 17359 39157 71359 93157 17395 39175 71395 93175 17539 39517 71539 93517 17593 39571 71593 93571 17935 39715 71935 93715 17953 39751 71953 93751 19357 51379 73159 95137 19375 51397 73195 95173 19537 51739 73519 95317 19573 51793 73591 95371 19735 51937 73915 95713 19753 51973 73951 95731 31579 53179 75139 97135 31597 53197 75193 97153 31759 53719 75319 97315 31795 53791 75391 97351 31957 53917 75913 97513 31975 53971 75931 97531 '----------------------- "seagee69" wrote in message As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
But the one you came up with is one I'll keep track of. It is determining
the permutations rather than combinations, and permutations are often called for also. Nice find as far as I'm concerned. "Jim Cone" wrote: Tom, Thanks for that, point taken. In fact the program removes duplicates when they do occur. Entries with duplicate characters have the results fed thru a collection to remove repeats. So, for instance, "food" produces this list of 12 items not 24... -- Regards, Jim Cone San Francisco, USA food dfoo dofo doof fdoo fodo odfo odof ofdo ofod oodf oofd '-------- "Tom Ogilvy" wrote in message Jim, just a head up. may not fill the bill so to speak. The numbers can repeat Your doing straight permutations which don't allow numbers to repeat. straight permutations: 5! = 120 permutations with numbers repeating: 5^5 = 3125 -- Regards, Tom Ogilvy "Jim Cone" wrote in message The free Excel add-in "Display Word Permutations" provided the following list. (the list is created in a single column) Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA 13579 35179 57139 79135 13597 35197 57193 79153 13759 35719 57319 79315 13795 35791 57391 79351 13957 35917 57913 79513 13975 35971 57931 79531 15379 37159 59137 91357 15397 37195 59173 91375 15739 37519 59317 91537 15793 37591 59371 91573 15937 37915 59713 91735 15973 37951 59731 91753 17359 39157 71359 93157 17395 39175 71395 93175 17539 39517 71539 93517 17593 39571 71593 93571 17935 39715 71935 93715 17953 39751 71953 93751 19357 51379 73159 95137 19375 51397 73195 95173 19537 51739 73519 95317 19573 51793 73591 95371 19735 51937 73915 95713 19753 51973 73951 95731 31579 53179 75139 97135 31597 53197 75193 97153 31759 53719 75319 97315 31795 53791 75391 97351 31957 53917 75913 97513 31975 53971 75931 97531 '----------------------- "seagee69" wrote in message As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
Not sure what you mean by "nice find". I found it by writing it<g
-- Regards, Tom Ogilvy "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... But the one you came up with is one I'll keep track of. It is determining the permutations rather than combinations, and permutations are often called for also. Nice find as far as I'm concerned. "Jim Cone" wrote: Tom, Thanks for that, point taken. In fact the program removes duplicates when they do occur. Entries with duplicate characters have the results fed thru a collection to remove repeats. So, for instance, "food" produces this list of 12 items not 24... -- Regards, Jim Cone San Francisco, USA food dfoo dofo doof fdoo fodo odfo odof ofdo ofod oodf oofd '-------- "Tom Ogilvy" wrote in message Jim, just a head up. may not fill the bill so to speak. The numbers can repeat Your doing straight permutations which don't allow numbers to repeat. straight permutations: 5! = 120 permutations with numbers repeating: 5^5 = 3125 -- Regards, Tom Ogilvy "Jim Cone" wrote in message The free Excel add-in "Display Word Permutations" provided the following list. (the list is created in a single column) Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA 13579 35179 57139 79135 13597 35197 57193 79153 13759 35719 57319 79315 13795 35791 57391 79351 13957 35917 57913 79513 13975 35971 57931 79531 15379 37159 59137 91357 15397 37195 59173 91375 15739 37519 59317 91537 15793 37591 59371 91573 15937 37915 59713 91735 15973 37951 59731 91753 17359 39157 71359 93157 17395 39175 71395 93175 17539 39517 71539 93517 17593 39571 71593 93571 17935 39715 71935 93715 17953 39751 71953 93751 19357 51379 73159 95137 19375 51397 73195 95173 19537 51739 73519 95317 19573 51793 73591 95371 19735 51937 73915 95713 19753 51973 73951 95731 31579 53179 75139 97135 31597 53197 75193 97153 31759 53719 75319 97315 31795 53791 75391 97351 31957 53917 75913 97513 31975 53971 75931 97531 '----------------------- "seagee69" wrote in message As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Every 5 digit combination
I saw that. I meant it not just as good "find" here, but as a good find for
me - I like not having to reinvent wheels. "Tom Ogilvy" wrote: Not sure what you mean by "nice find". I found it by writing it<g -- Regards, Tom Ogilvy "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... But the one you came up with is one I'll keep track of. It is determining the permutations rather than combinations, and permutations are often called for also. Nice find as far as I'm concerned. "Jim Cone" wrote: Tom, Thanks for that, point taken. In fact the program removes duplicates when they do occur. Entries with duplicate characters have the results fed thru a collection to remove repeats. So, for instance, "food" produces this list of 12 items not 24... -- Regards, Jim Cone San Francisco, USA food dfoo dofo doof fdoo fodo odfo odof ofdo ofod oodf oofd '-------- "Tom Ogilvy" wrote in message Jim, just a head up. may not fill the bill so to speak. The numbers can repeat Your doing straight permutations which don't allow numbers to repeat. straight permutations: 5! = 120 permutations with numbers repeating: 5^5 = 3125 -- Regards, Tom Ogilvy "Jim Cone" wrote in message The free Excel add-in "Display Word Permutations" provided the following list. (the list is created in a single column) Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA 13579 35179 57139 79135 13597 35197 57193 79153 13759 35719 57319 79315 13795 35791 57391 79351 13957 35917 57913 79513 13975 35971 57931 79531 15379 37159 59137 91357 15397 37195 59173 91375 15739 37519 59317 91537 15793 37591 59371 91573 15937 37915 59713 91735 15973 37951 59731 91753 17359 39157 71359 93157 17395 39175 71395 93175 17539 39517 71539 93517 17593 39571 71593 93571 17935 39715 71935 93715 17953 39751 71953 93751 19357 51379 73159 95137 19375 51397 73195 95173 19537 51739 73519 95317 19573 51793 73591 95371 19735 51937 73915 95713 19753 51973 73951 95731 31579 53179 75139 97135 31597 53197 75193 97153 31759 53719 75319 97315 31795 53791 75391 97351 31957 53917 75913 97513 31975 53971 75931 97531 '----------------------- "seagee69" wrote in message As a newby to Excel VBA, I'd like to create a spreadsheet that would give me every 5-digit combination of the numbers {1, 3, 5, 7, 9} in one spreadsheet. The numbers can repeat. I thought of the FOR NEXT loop, but I am not sure how to get the output to the next row, nor am I certain it would give every combination. Thanks so much, seagee69 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Permutation combination of a 4 digit no | Excel Discussion (Misc queries) | |||
| combination of six digit number from 0 to 49 | Excel Worksheet Functions | |||
| Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) | |||
| Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel | |||
| When we enter a 16 digit number (credit card) the last digit chan. | Excel Discussion (Misc queries) | |||
Linear Mode
