Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-01-2022, 02:11 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 07-01-2022, 05:15 AM
jeffreybrown jeffreybrown is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

All credit due to shg. Give this product a try.

Here is a post you can follow at post #5.

It doesn't appear the product is downloadable anymore, but the attached is it.
Attached Files
File Type: xlsm Mr545687 - Permutations of Combinations.xlsm (107.3 KB, 5 views)
Reply With Quote
  #3  
Old 07-01-2022, 09:38 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default

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)
Reply With Quote
  #4  
Old 07-01-2022, 10:04 AM
jeffreybrown jeffreybrown is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

This could be built into the code, but the easiest method would be to use Text to Columns

Text to Columns
  • Highlight range to convert
  • Data >> Data Tools >> Text to Columns >> Check Delimited >> Next
  • Check Space >> Finish >> OK
Reply With Quote
  #5  
Old 07-01-2022, 10:09 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 07-01-2022, 10:25 AM
jeffreybrown jeffreybrown is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Not sure why, but try this...
Attached Files
File Type: xlsm MrE654168 - Combinations.xlsm (353.6 KB, 5 views)
Reply With Quote
  #7  
Old 07-01-2022, 10:32 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 07-01-2022, 10:37 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 07-01-2022, 11:14 AM
jeffreybrown jeffreybrown is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Reply With Quote
  #10  
Old 07-01-2022, 11:36 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default

Sample.xlsx

Hi

I (think) I have attached a sample worksheet...
Reply With Quote
  #11  
Old 07-01-2022, 11:38 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 07-01-2022, 12:01 PM
jeffreybrown jeffreybrown is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Reply With Quote
  #13  
Old 07-02-2022, 05:22 AM
p45cal's Avatar
p45cal p45cal is online now Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2019
Expert
 
Join Date: Apr 2014
Posts: 639
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

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?!
Attached Files
File Type: xlsm msofficeforums49289Sample.xlsm (21.5 KB, 5 views)
Reply With Quote
  #14  
Old 07-02-2022, 05:30 AM
Paulrwoolley Paulrwoolley is offline Permutations of a list of numbers Windows 10 Permutations of a list of numbers Office 2021
Novice
Permutations of a list of numbers
 
Join Date: Jul 2022
Posts: 8
Paulrwoolley is on a distinguished road
Default

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!
Reply With Quote
Reply

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
Permutations of a list of numbers 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
Permutations of a list of numbers How to add 10000 numbers in a list without dragging down Pleasehelpmewithexcel Excel 3 12-18-2015 06:46 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft