Random List Selection w/No Duplicates

[RDSProgrammer]

I need to fill 5 cells with random selections from a named range (rgnItems) without any of the 5 cells containing duplicate values from the others.

Is this possible with an Excel function?

Right now I have a formula in each of the 5 cells that randomly selects a value from rgnItems, but of course I am getting duplicates sometimes across my 5 cells.

[Flintstone]

Hello RDSProgrammer:

In cell A1 enter the RAND() function and copy down as far as needed, column B is your list of values. Paste this formula in cell D1 and copy down to row 5.

Please Login or Register  to view this content.

Matt

[RDSProgrammer]

That worked--but, I have a question regarding this technique.

It seems as though I need to include a rand() value in a cell for each cell in rgnItems. However, our users will be adding entries to rgnItems themselves.

Is there a way so that the users won't have to also add the rand() function to the cell in the column next to the entry that they add? I don't think that they will understand or reliably do this. Also, we can't use VBA for any of this in our situation.

[shg]

Create an out-of-the-way range, starting in, say, Z1. It can be on the same sheet (and you can hide it) or a different sheet. Then in Z1 (or wherever it starts), enter and copy down =RAND() for enough cells to always exceed the rows in rgnItems.

Then define rgnRand Refers to: =OFFSET($Z$1, 0, 0, ROWS(rgnItems))

Then use this adaptation of Flintstone's formula in the same rows as rgnItems:

=INDEX(rgnItems, MATCH(SMALL(rgnRand, ROW() - ROW(rgnItems) + 1), rgnRand, 0 ))

[Flintstone]

Replace the RAND() functions with this formula, should do it.

Please Login or Register  to view this content.

Matt

[RDSProgrammer]

That did the trick.

I used an IF formula around the rand() function and everything works like a charm.

Thanks to all.