I’m trying to generate 26 random numbers with no repeats.
Background.
I’ve created a “code-breaker” spreadsheet in Apple Numbers to make maths more fun for my children. The basic concept is that I write a sentence, e.g., “Cats are cool”, and the spreadsheet generates a key with sums. The kids solve the sums and use the key to reveal the sentence.
The problem is that the result numbers often have duplicates. This rarely translates to duplicates in the code itself because the code doesn’t use all 26 letters, but it has happened a couple of times.
Anyone have any ideas?
Some relevant points:
An Excel solution is fine as I can either find a Numbers equivalent or just use Excel (Numbers is my preferred because it is prettier, but Excel is more powerful).
I would like to stick with random numbers because I don’t want to have to create the sums manually and I want them to be different each time.
I’ve tried limiting the scope of the random number associated with each letter but it loses apparent randomness (you know that “A”, for example, will always be a number between 1 and 20).
Edit to add an illustrative pic:
The two columns of numbers in the Key sums are randoms between 1 and 99. The code pulls the answers from those sums and places them below the appropriate letter in the code.
To create 26 random integers between 1 and 99 I would do the following.
In excel create 99 random (real) numbers using the formula =RAND() in cells A1:A99
Copy and paste as values so they don’t keep changing
In B1 enter the following formula
=RANK(A1,$A$1:$A$99,FALSE)
Copy this formula down to B2:B26
B1:B26 will then contain your 26 numbers between 1 and 99 without duplicates
However do you want the sums to be unique? In that case I think your case option is to just create a series of pairs of numbers between 1 and 99 and add the pairs if the sum of a pair is the same as the sum of a previous pair skip it. You can check if it is a duplicate using the formula:
=MATCH(cell containing sum for this pair, range containing sum of all previous pairs, FALSE)
If the answer is #N/A then the answer is unique, if it is a number it marks the location of the duplicate.
Thanks, that worked. I kept the RAND() as is because I want the numbers to change. I’ve found a solution in Numbers as well. The sums themselves would ideally be unique but it’s not as important and they are less likely to be duplicates anyway.
The RAND function in Excel is not really random (and I assume the function in Numbers is similar). For casual use it’k OK but it can get you into trouble.
20 years ago I was using Excel to develop some models that required “true” random number generation. The RAND function gave squirrelly results. I ended up using the Poptools plugin (which apparently is still available), which replaced all the standard Excel random number functions. The Poptools random number generator uses the mersenne twister psedorandom number generator algorthm, which is the best random number genrator available. Worked great.
The operations where pseudorandom numbers aren’t sufficient, but that can be done in Excel (and Mersenne Twister is pseudorandom as well), are exceptionally rare.
While this can be handled with a bit of code, it sounds like you don’t mind doing a touch of manual work. If so, the easiest way I can think of is this nested function that generates more than the 26 numbers you need, but you can just delete any of them beyond the 26th row.
This assumes numbers between 1 and 100 are desired, but you can change that as needed.
That is almost exactly what I did except I didn’t nest the RANDARRAY (just because I’m dumb and didn’t know I could). It doesn’t require any manual work as I have a filter that hides all rows that don’t relate to a letter in the code.
If everyone who was an expert in all of the Excel functions had a get together, they could probably host it in a small conference room (and I wouldn’t be eligible). You’re not dumb, you’re just one of us.
