number permutation formula

  • Thread starter Thread starter rally
  • Start date Start date
R

rally

hi,

is it possible using excel to show results of number permutations?
using some functions or macros or formula. eg if i enter a number 123,
results returned should be 321, 213, 132 etc..

thn
 
Rally,

Check out this previous post on a technique by Myrna Larson
http://tinyurl.com/273h5

If you put P in A1, 3 in A2, 1 in A3, 2 in A4, 3 in A5, and then run
ListPermutations. It will return 1,2,3 and 2,3,1 and 3,1,2 etc, but you can
strip the commas with =SUBSTITUTE(A1,",","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
hi Bob,

im a newbie user, so is there any online help for the functio
ListPermutations?

I tried copy/paste the reply from the other thread and i go
"overflow".

im cant find any help for Listpermutations in the VBA help too.

thn
 
There is no online help for this as it is a custom built function.

How many numbers are you trying to perm?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
hi Bob,

im trying to perm 4 digit numbers so max permutation is 24.
i want to put a figure say 1234 in cell A1 and the resulting 2
permutation be shown in another cell(s). A2-A25 something like that
4321, 2314, 4213 etc..
is this a very difficult procedure to learn?

from your earlier thread i can use =Substitute to remove the comma
right?

maybe you could tell me which section this comes under in VBA so
could do some reading up.

thnx in advanc
 
rally > said:
im trying to perm 4 digit numbers so max permutation is 24.
i want to put a figure say 1234 in cell A1 and the resulting 24
permutation be shown in another cell(s). A2-A25 something like that.
4321, 2314, 4213 etc..
is this a very difficult procedure to learn?
Click to expand...
....

This could be done using worksheet functions only. If you enter 1234 in A1
and would want the permutations in A2:A24, enter the following formula in
A7.

A7:
=IF(MOD(ROW(),2)=0,F6+9*(MID(F6,4,1)-MID(F6,3,1)),
CHOOSE(MOD(ROW()-1,6)/2+1,F1+TRUNC(999,INT((ROW()-1)/6)-3),
F5+90*(MID(F5,3,1)-MID(F5,2,1)),F5+99*(MID(F5,4,1)-MID(F5,2,1))))

Copy A7 and paste into A2:A24. You could then use the digits in these as
indices to permute any other array. This could be adapted for 5 and 6 item
arrays, but the formulas get long and ugly quickly. The trick here is that
the difference between any two numbers with transposed digits is a multiple
of 9.

You might also see the thread

http://groups.google.com/[email protected]

which gives some alternatives that are a bit shorter and simpler than Myrna
Larson's.
 
listpermutations .

List Permutations by Myrna displays the 3 numbers subsets of a ( 1 ) set 1 2 3 4 5 6 in a column .

1
2
3
4
5
6

123
124
125
etc...

If the SETS are >1 and listed in A TABLE ( column ) ,
123456
123457
etc...

how to modify the VBA to display the results in ROWS ( SHEET ) ?

123 124 125 ....
136 137 etc...

Thank you .
 
Back
Top