Randomizing a list

Hi

You need to have the Analysis Toolpak Add-in loaded to use this function. If it is not loaded, you will get this #NAME error.

Cheers
P.K.Hari

Hi again,

If you want to use VBA instead, or are unable to install the add-in, you can check out the following link:
http://www.exceltip.com/st/An_Improved_Custom_Random_Numbers_Function/632.html

Cheers
P.K.

Thanks--it works now.  I should have known to check there, but I just assumed too much!  

Hi,

I would just like to add a comment here to show how nicely this function can be used in some specific cases.

For instance, when you want to draw lots amongst employees of a Company for prize distribution or for mutual gifting selection etc, it is really required to have a random choice. Normally, we used to achieve it by drawing lots from a box.

Instead, the following can be done to meet this need:

a) Put down the list of employee's names in Column A in a worksheet
b) In Column B, say in B1, (if you have started the names from A1 itself), try this formula:
=OFFSET(A1,(RANDBETWEEN(1,COUNT(A:A))),0)
c)The result you will get here is a pick from the list of names appearing in Column A, very randomly picked (REALLY RANDOM - AS GOOD AS DRAWING FROM A LOT).
d) This formula can be copy-pasted to other cells below too.
e) Just in case you want to change a name appearing in the list, at any point, you can press F9 key, which will order a recalculation, and hence, will automatically change the names appearing in B.

Infact, RANDBETWEEN() can be used in so many more scientific workings that I wish that it is made a part of the standard functions list. Unfortunately, it is a part of the Analysis ToolPak Add-in, and only if this add-in is installed and available, you can use this function.

Hope this helps.

Cheers
P.K.

Hello,

I had better luck with:

=OFFSET(A1,(RANDBETWEEN(0,COUNTA(A:A)-1)),0)

Joseph

Hi Joseph

That's just wonderful !! I just wanted to show an example of the function and am glad to see that it has already set the ideas blazing in you.

Thanks for your post.

Cheers
P.K.

Hi,

I followed the link from Experts Exchange newsletter to this question. Good stuff!

My side of our family is a little more complex. There are 5 married siblings. Four of these couples have children.

When we draw names there are two separate groups - children and adults.

**The challenge is that within each group no one should draw a name from their own family.**
The solution can be in any office app, incuding Access.

I have posted a new question at https://www.experts-exchange.com/questions/20809111/Create-Random-Holiday-Gift-List-Without-Picking-Sibling-or-Spouse.html which includes the list of adults and children.

500 points for a solution to this problem. 500 bonus points if the solution comes before we plan to draw names at 3 PM Central Time Thanksgiving day.

Thanks!

John

Yeah.  Make sure you use JMarreiros's formula and not Pkhari's.  It wouldn't work until I added the -1.

Another way is to use
=OFFSET(A1,(RANDBETWEEN(1,COUNTA(A:A))),0)
but just the names in cell A2.

I'll use this for the Christmas draw at work.

Regards

Mark

A little frivolity...

Have two cells, one randomizing (Ace, King, Queen etc.) the other randomizing (Spades, Hearts etc.) and you can deal yourself a nice little poker game! Thing is, how do you ban it from dealing the same card twice?

there's 125pts on it, more if the answer's imaginative, fun and interesting!

http://oldlook.experts-exchange.com/questions/20810477/Excel-Poker.html

I have another way to randomize without using the Analysis Toolpak Add-in.

To get a random list, I use
=RAND()
in a seperate column, then sort the results using that random column.  

Each time you sort the column, your random numbers will change so you will get a different order.

Dennis

Yeah, but that's for working with numbers.  We're working with names of people here.

This does work with names. For example:

  A           B
Alan    =Rand()
Bob     =Rand()
Chris   =Rand()
Doug   =Rand()

When you select both columns, and sort by column B, you will get a random order every time you sort.

Put the list of names in A. Copy the list to B. Put  =rand() in column C and copy it down next to the list. Record a macro called draw_names to select columns B and C and sort using column C. Give it ctrl+ d as a keystroke shortcut. In D put the function =if(A1=B1,"RERUN"," ") and copy it down. Hide column C. Now use ctrl+ d to run the macro. If it says RERUN anywhere in D rerun the macro. You will have a list of givers and receivers.