Random number with no repetition?

[Terry Pinnell]

While on holiday recently I was given a sheet of about 50 anagrams to
work out. Like

DOECARROT (Answer: DECORATOR)
PETRESCUE
TALKSENSH
FETIDSITE
GUNGIBILE
etc

In between walking the coastal paths of Cinque Terre in Northern Italy
I spent many hours at these, managing to solve about 15 of them. After
some time my thoughts turned towards cheating. I had my iPAQ 2210
Pocket PC with me, and it has Pocket Excel installed. It's very
limited by comparison with my PC's Excel 2000. No VBA macros for
example. But I set about trying to write a spreadsheet which would let
me enter e.g. PETRESCUE and see say 20 random anagrams, click a button
or press a key and see another 20, and so on. Even if not delivering
the answer itself (there are nearly 370,000 permutations of a 9
character word!), I thought it might be helpful. Rather like shuffling
your 7 Scrabble letters to get inspiration.

But I quickly came across what seemed an impossible hurdle. Using the
RAND() function (as part of this) inevitably gives a 'repetitive'
result. For example, if I developed 9 digit numbers (with the aim of
using them to shuffle the original 9 letters around), they would be
like 981245331, 123467238, 331245678, etc. IOW, one or more digits
would often be repeated.

So ... can anyone suggest a method of developing a set of 9 digit
numbers with *no* repetition? Or, of course, solving the problem
directly, i.e. developing a set of unique anagrams of a 9 character
string?

 

[Guest]

Hi Terry:

In A11:
DOECARROT

In A1 thru A9:
=MID($A$11,1,1)
=MID($A$11,2,1)
=MID($A$11,3,1)
=MID($A$11,4,1)
=MID($A$11,5,1)
=MID($A$11,6,1)
=MID($A$11,7,1)
=MID($A$11,8,1)
=MID($A$11,9,1)

In B1 thru B9:
=RAND()

In C1 thru C9:
=INDIRECT("a1",1)
=INDIRECT("a2",1)
=INDIRECT("a3",1)
=INDIRECT("a4",1)
=INDIRECT("a5",1)
=INDIRECT("a6",1)
=INDIRECT("a7",1)
=INDIRECT("a8",1)
=INDIRECT("a9",1)

Finally in D1:
=C1 & C2 & C3 & C4 & C5 & C6 & C7 & C8 & C9

You should see something like:

D 0.788697018 D DOECARROT
O 0.459787941 O
E 0.043096269 E
C 0.549214109 C
A 0.487866601 A
R 0.97915452 R
R 0.649401062 R
O 0.304188787 O
T 0.876702257 T

DOECARROT


Now just sort A1 thru B9 by column B to :

E 0.534497582 E EOOACRDTR
O 0.803127116 O
O 0.512083136 O
A 0.510013841 A
C 0.057257221 C
R 0.722737488 R
D 0.379801253 D
T 0.004744099 T
R 0.216199846 R

DOECARROT

Each repeated sort wil lre-shuffle the letters in D1

 

[Terry Pinnell]

Excellent, thanks a bunch!