in a spreadsheet column of 300 titles, how can I generate a list of say, 25 randomly selected ?

You could do something like this:

The 'Titles' table has 301 rows.

The formula in A2 of the 'Selection' table is:

=INDEX(Titles::A,RANDBETWEEN(1,300))

Note that this is sampling with replacement. In other words, the same title might be selected twice.

If you need selection without replacement, it gets more complicated.

SG

One further thought: If you need a selection of 25 unique entries (no duplicates) then you can easily test for duplicates like this:

=COUNTIF(A,A2)

If you see a number greater than 1 then you know the title on that row is a duplicate and can either make another selection or replace that one title with something different.

(There are complicated ways to fully automate, but for most purposes this should do the trick.)

SG

Barry, what about this:

in column B: = RANDBETWEEN(2,275)+25 (to limit the number to 300 titles)

in column C: INDIRECT(ADDRESS(B2-ROW(),1) to get a list of 25 randomly selected titles

The trick to get unique titles is to combine a random number (generated in column B) with the actual row in column C (with a maximum of 25 titles).

Paul.

I tested the more complicated INDIRECT RANDBETWEEN ADDRESS ROW approach. It seems to be just as susceptible to providing duplicate values as the simpler INDEX RANDBETWEEN approach (duplicates marked in red).

For a table with one Header Row followed by 300 body rows for 301 rows in total my original formula should be revised to this:

=INDEX(Titles::A,RANDBETWEEN(2,301))

If there are no header rows then the formula originally suggested works.

Substitute ; for , in the formula if your region requires it.

SG

It's funny. normally SGIII will suggest an AppleScript and I will suggest a spreadsheet method. Today is different.

I suggest using an AppleScript.

select this link to download and example document from here:

Dropbox - SelectRandomList.numbers

Open this document. There is a single table with Item Numbers in column A. In column B are titles which I was too lazy to type more than a single letter.

Now make sure that AppleScript is enabled on your computer:

Now (I don't know if this step is really necessary) quit Number and restart Numbers. If the Script menu is already showing in Numbers, then there is no need.

The script menu looks like this in the menu bar:

Open the Number script folder for your user by making Numbers the active app, then select the menu item :

Now download this AppleScript and save in the folder you just opened

To see the example run, open the example and make this document the active Numbers document (just click the canvas, or background), then select the script from the Numbers Script folder:

Now select script from Numbers Script menu:

Now select how many entries you want from the list:

Final result:

to create a new, table to one of the following:

- rename the Table named "List" to some other name ... like "List 1"

- delete the table named "List"

Use the example. and post back here specific questions to adapt it to your situation.

Here's another way to produce a randomly sorted table, from which you can select the first 35 rows (or any number of rows) to get a random selection of n titles from the list. This method will not produce any duplicates (unless those are titles duplicated in the original list), as it sorts all the rows into a random order rather than selecting individual titles from the list.

First nineteen rows of the original list:

First nineteen rows of the list after clicking the checkbox to generate a new set of random values in column C, then sorting on column C:

…and after one more click on the checkbox, and a click and select on the contextual menu in the reference tab for column C:

(Note the change in the values in column C)

…and after releasing the mouse button to complete the sort:

Regards,

Barry

Hi Barry T,

I wouldn't rely on the list to be "in Order", I always make that judgement based on the audience, the venue, the situation.

This may (or may not 😉) work for you.

Here is a table with an imaginary subset of your 300+ songs.

In Column B you have identified each song as suitable for "1 Opening" etc. to give some order.

Click on the checkbox in D1 to create a new column of random numbers. Column D contains the RAND function that Barry suggested.

Sort Ascending by Column D

Then sort Ascending by Column B (Category)

Next, choose (click on a checkbox in Column C) your preferred song(s) within each Category, depending upon your audience for that concert.

Sort Descending by Column C (Choose)

In this example, your play list would be songs titled A, O, M, H, J, I, E, S, R

Regards,

Ian.

Edit: The screen shots may not match. I added another screen shot and RAND gave me a different order 😊. Ian.

Hi Barry,

I think you'll find if you have your song titles in a table named Titles that

=INDEX(Titles::A,RANDBETWEEN(2,301))

will be easy to input and to use.

Just be sure to change the 301 to the number of the last row of the tables with the titles.

SG

barrythorpe wrote:

. am I naive to think there would be a simple command ? :" Pick 30 from list and print" ......

Yes, in spreadsheetese that's what the formula says .🙂

=INDEX(Titles::A,RANDBETWEEN(2,301))

The RANDBETWEEN() here simply says pick a random number between 2 and 301. It feeds that number to INDEX(), which tells Numbers to get the item on the list that matches that number, with 2 meaning the 1st item (you have to subtract 1 to account for the Header Row), 3 meaning the 2nd item, etc.

You copy that simple formula down to the cells beneath it in the column, and they each do the same thing, giving you a list of 30.

The printing part, if you need that, you have to do yourself via File > Print in the menu.

SG

Hi Barry,

Two comments:

1. A simple way to avoid duplicates is to select several more titles than you actually need. For a set list of 25, use the methods described above to select a list of 30 random titles. Print the list, then strike out the 'extra' copies of any duplicates, plus enough others to reduce your list to 25 titles.

2. Set lists usually involve some thought as to how the songs go together (and whether some combinations fit together in any imaginable way). A random selection may give you a useable program, but it's likely that some added thought given to the cumulative effect that ordering that program will have. Which pieces transition well to which other pieces? What effect is 'this piece' going to have on the audience? How will that effect be enhanced (or countered) by the piece that follows? You may need to expand on the technique described in 1 to give enough titles to let you throw out the ones that don't fit the program.

Regards,

Barry

The reason I posted the script version is because the only way to guarantee a random list which is a subset of an original list is to do a method I proposed some time ago in other threads that removes a single, random item from the existing list, then repeats until the number of items are removed.

the removed items comprise the randomly select list. The method I proposed was done using a spreadsheet but since Numbers only allows 256 columns my solution would not work for this OPs problem.

The idea is this:

start with a list of values:

A B C D E F G H I J K

get a random Number... between 1 and 11, so remove item rand = 4

A B C E F G H I J L Removed List is : D

get a random Number... between 1 and 10, so remove item rand = 6

A B C E F H I J L Removed List is : D G

get a random Number... between 1 and 9, so remove item rand = 1

B C E F H I J L Removed List is : D G A

etc

The script performs this task