Excel Formula

[larkyfarken]

I am trying to take a list of 18 people in a column, and create a random list of 5 and 4 without repeating names within the smaller lists. I've tried with a few formulas I found online, but they don't guarantee no duplications, and when I try to F9 it to recalculate, it changes all values in the lists I've just created. Please help!!

[Bryan Hessey]

I am trying to take a list of 18 people in a column, and create a random list of 5 and 4 without repeating names within the smaller lists. I've tried with a few formulas I found online, but they don't guarantee no duplications, and when I try to F9 it to recalculate, it changes all values in the lists I've just created. Please help!!

Hi,
try the attached

Amend the numbers 1 to 18 in G8 onwards to any desired values

---

[daddylonglegs]

I am trying to take a list of 18 people in a column, and create a random list of 5 and 4 without repeating names within the smaller lists. I've tried with a few formulas I found online, but they don't guarantee no duplications, and when I try to F9 it to recalculate, it changes all values in the lists I've just created. Please help!!

If you want to create a "static" non-repeating list perhaps try the MRAND function from the Morefunc add-in, e.g. if you have your 18 names in A1:A18
use the formula

=INDEX(A1:A18,MRAND(4,1,ROWS(A1:A18),TRUE))

confirmed with CTRL+SHIFT+ENTER

You can get Morefunc ad-in from here

http://xcell05.free.fr/

[Json]

Thank you for posting this link

[Bryan Hessey]

Hello,

this is larkyfarken. I saw your reply to my post. I downloaded your worksheet and took a look at it. I tried to understand the math so that I could set it up in one worksheet, and get the results in another worksheet. However, I'm having trouble following the math behind the results in order to get the desired outcome. Could you please help?? Thanks.

Ben

Hi,

The formula effectively 'rank's the ivert range (1 to 100) depending on the count of items found compared to the random value in the row of the rvert range.

It means that it is easy to randomly shuffle a list of numbers or names (replace 1-100 by 'George, Ted, Alice etc).

To set this in another sheet you would need to amend the formula at B2 to F4 from =J8 to =Shuffle!J8 etc.

You will also need a button or macro to re-shuffle your list on the other sheet, as a Macro, set (say) a shortcut key of CTRL/Shift/S and a code for that of

Please Login or Register  to view this content.

Let me know how you go.
---