This is a up-to-date variation of some docs I found useful, the original was made by johnSUN.

My problem

Today I was researching for a possible change in the phpList UI: right now the behaviour of the menu item “Send a campaign” depends on if you have draft campaigns or not. If you have no drafts, a new campaign will be created: if you have draft campaigns already, you will be taken to the drafts and given a button to make a new campaign.

I wanted to check a random sample of phpList clients to see if they have unused draft campaigns going back months/years (like me) or if they “keep things tidy” and use/delete all their drafts (like our lead developer).

Generic version of problem

You have a bunch of data and and you want to randomly select a sample of it to check out something.

How to get your random sample

The process is essentially to generate a random number between 0 and 1 for every data entry and then sort the data in numerical order. So if you have:

Anna, Ben, Joey, Sam

You could do

Anna, 0.5, Ben 0.2, Joey 0.1, Sam 0.8

and sort them like:

Joey 0.1, Ben 0.2, Anna 0.5, Sam 0.8 or vice versa.

Create your random numbers

Your data needs to be in one column (column A) and any order (mine is alphabetised).

Click in cell B1 and type:

=RAND()

hit return. This creates on random number. Click the little box in the bottom right of the cell to auto fill column B. You now have a random number for every entry in column a next to it in column b.

Define database range

I guess this works like a “landing spot” for your results, it means you don’t need to change your original list.

First create a new tab, you could call it “Results”

Then select cell A1 and choose Data > Define Range from the main menu.

The Name should be something like ResultOfSort then under Options choose Insert or delete cells and leave the rest as it is.

Click Add then click OK.

Sort the data

Now go back to your original sheet and select both column A and B. Go Data > Sort from the main menu. In the first tab of the Sort dialogue click Column B for Sort Key 1.

Then go to the second tab Options and choose the your ResultOfSort databse range from the Copy sort results to: dropdown. 

Click OK.

Then delete the second column. The random numbers get re-generated I think, which is why in the diagram above they are no in ascending order as you would expect.

Select your sample

If you only wanted a sample of, for example 100 entries, then you can simply take the first 100 now that they are randomised. If you wanted 200, take the first 200.

Enjoy!