Excel Puzzle - Eliminating Random Numbers in a Diminishing Sequence

[gjohn282]

Good Afternoon Everyone,

I'm trying to figure out how to take advantage of Excel's random (rand) function in order to come up with a formula to solve this puzzle...

Puzzle: You have 9 pieces of paper, each with a different number on it ranging from 1 to 9. There are no repeat numbers; simply each paper as the either 1, 2, 3, 4, 5, 6, 7, 8, or 9. You randomly select one of the pieces of paper, and record the number in cell A1. You repeat this process in cells A2 through A9. Your sequence of numbers is completely random. How can I mimic this in Excel?

The formula in cell A1 is easy enough: =RANDBETWEEN(1,9)

What would I put in cells A2 through A9 so that it eliminates the number(s) that have already been used? I gave up after a few days of trying to figure this out and started building tables to put together a process of elimination, but still haven't quite figured it out. I'm sure there's an easier way. Thanks!

Note: I do want this to be completely random, so that I can use the F9 key and record various outcomes.

[Moo the Dog]

Put the numbers 1 - 9 in cells A1:A9 (A1=1, A2=2, etc.)
In B1 to B9 enter the function: =RAND()
In C1 enter this formula and fill down to C9: =INDEX($A$1:$A$9,RANK(B1,$B$1:$B$9))

Column C will now have a list of numbers 1 - 9 without duplicates.

- Moo

(h/t to Domenic on another Excel help forum, in a post from 2006)

[Rambo4711]

Hi, gjohn282

Put this formula i cell A2:

=SMALL(IF(COUNTIF(A$1:A1,ROW($1:$10))<>1,ROW($1:$10)),1+INT(RAND()*(9-ROW()+ROW(A$1))))

Confirm formula with Ctrl+Shift+Enter (not just Enter)

Then the formula show:

{=SMALL(IF(COUNTIF(A$1:A1,ROW($1:$10))<>1,ROW($1:$10)),1+INT(RAND()*(9-ROW()+ROW(A$1))))}

Copy the formula down to cell A10

[gjohn282]

Wow, both are excellent methods. Thank you for the replies!

[Moo the Dog]

Glad to help, and thanks for the Rep. It's always appreciated!

Happy Thanksgiving!

- Moo