#1
|
|||
|
|||
Permutations of a list of numbers
Hi
I am not very competent in Excel.. I have a list of numbers: 2 3 5 7 11 13 17 19 23 29 31 37 41 (i.e. the first 13 prime numbers). I would like Excel to return a list showing all the (8000+) permutations of those numbers. Eg if it was just the numbers 1 2 3, Excel would return: 1 2 3 1 3 2 2 1 3 2 3 1 3 1 2 3 2 1 If a kind reader would be able to just send me a link to a file that has this done already, it would be greatly appreciated as I do not understand 'modules'!! Many thanks! |
#3
|
|||
|
|||
Thanks Jeffrey.
Do you know how I can adapt this so that each of the elements of the permutation appear in separate cells? (So rather than cba appearing as a single string of letters, it appears c b a each in a separate cell) |
#4
|
|||
|
|||
This could be built into the code, but the easiest method would be to use Text to Columns
Text to Columns
|
#5
|
|||
|
|||
Thank you - I will try that.
I have entered my 13 primes into cell C7 with a space between each. However, when I click 'Do it@ nothing happens, other than D6 and D7 updating. The list of combinations remains as CBA etc. Any ideas? Thank you in advance. |
#6
|
|||
|
|||
Not sure why, but try this...
|
#7
|
|||
|
|||
Thanks - it still doesn't seem to do anything.
I have changed A5 to 13, which I understood would only list the selections that contain all 13 primes, but it just stays the same when I hit List 'em |
#8
|
|||
|
|||
Also, it seems that the returned values are just 'selections' of the 13, rather than 'permutations'.
So A B C will give A B C A B A C B C A B C Rather than A B C A C B B A C B C A C A B C B A |
#9
|
|||
|
|||
In that workbook I already ran the code. Look in column B. It lists values all the way down to row B8111. Then starting in C12 and down to O8111, everything is separated into different cells.
If this is not it, can you post a workbook showing a very small sample of what you expect. |
#10
|
|||
|
|||
|
#11
|
|||
|
|||
So it is a list of all the possible combinations that exist of arranging the 13 numbers.
To explain using letters, it would be all the ways of arranging the letters from A to M so that each letter appears once only. |
#12
|
|||
|
|||
Hi Paul,
This still doesn't really resonate in my mind. In column A thru column J all of the numbers are the same, but then starting in column K numbers start to get jumbled. I don't think I know what to do here for you. Hopefully one of the "real" Excel experts can stay in and fix this. |
#13
|
||||
|
||||
Paulrwoolley, if, as you say just 3 numbers (1,2,3) lead to 6 permutations then 13 numbers will lead to 6.23 billion permutations (6.23 *10^9).
I stole some code from https://digitalcommons.wayne.edu/cgi...&context=jmasm and without looking at it too closely I made a few tweaks. It outputs the permutations to a text file (hard coded in the macro so you'll have to adjust that). It uses the value in cell B4 of the active sheet to decide how many numbers of your prime number sequence to permute; I chose 10 because it didn't take too long (just over a minute to produce 3.6 million permutations in the text file (sized 135Mb)) Choosing 13 looks like it'll take some 40 hours and might produce a 230Gb file. It takes the prime numbers from the left of cells A3:M3 of the active sheet (as many numbers as the value in B4). See attached. There's a button to press to run the macro. As you'd expect, the text file starts of with: 2 3 5 7 11 13 17 19 23 29 2 3 5 7 11 13 17 19 29 23 2 3 5 7 11 13 17 23 19 29 2 3 5 7 11 13 17 23 29 19 2 3 5 7 11 13 17 29 19 23 2 3 5 7 11 13 17 29 23 19 2 3 5 7 11 13 19 17 23 29 and finishes with: 29 23 19 17 13 11 5 7 3 2 29 23 19 17 13 11 7 2 3 5 29 23 19 17 13 11 7 2 5 3 29 23 19 17 13 11 7 3 2 5 29 23 19 17 13 11 7 3 5 2 29 23 19 17 13 11 7 5 2 3 29 23 19 17 13 11 7 5 3 2 What are you going to do with all these data?! |
#14
|
|||
|
|||
Thank you for your response.
I was hoping to use it to assist with a particularly tricky numerical crossword... ...however with that many permutations I will have to give it a miss! The benefit, though, has been learning a little more about visual basic editing, so it has not all been in vain! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Aligning numbers (to the right) in a numbered list | OneNote45 | PowerPoint | 1 | 03-09-2021 07:51 AM |
parsing numbers from a list | JBF | Excel | 2 | 10-14-2018 12:33 PM |
Pasting With Original Numbers From List | BarnabyS | Word | 15 | 08-16-2018 11:53 PM |
Picking Highest numbers out of List | JBF | Excel | 7 | 03-12-2018 01:14 AM |
How to add 10000 numbers in a list without dragging down | Pleasehelpmewithexcel | Excel | 3 | 12-18-2015 06:46 AM |