Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

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

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

Macbook

Posted on

Reply
Question marked as Helpful

Nov 15, 2017 6:53 PM in response to barrythorpe In response to barrythorpe

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

Question marked as Helpful

Nov 16, 2017 4:16 AM in response to barrythorpe In response to barrythorpe

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

There’s more to the conversation

Read all replies

Page content loaded

Nov 15, 2017 7:04 AM in response to barrythorpe In response to barrythorpe

You could do something like this:


User uploaded file



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

Nov 15, 2017 7:04 AM

Reply Helpful

Nov 15, 2017 7:46 AM in response to barrythorpe In response to barrythorpe

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


User uploaded file


=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

Nov 15, 2017 7:46 AM

Reply Helpful

Nov 15, 2017 11:12 AM in response to barrythorpe In response to barrythorpe

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.

User uploaded file

Nov 15, 2017 11:12 AM

Reply Helpful

Nov 15, 2017 11:43 AM in response to barrythorpe In response to barrythorpe

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).


User uploaded file


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:


User uploaded file


=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

Nov 15, 2017 11:43 AM

Reply Helpful

Nov 15, 2017 11:44 AM in response to stfflspl In response to stfflspl

Thank you.....this is a bit complex, but I appreciate knowing that.

For clarity, I'm a musician and have about 300+ songs that I typically draw from to play a gig. I was just hoping to go in on a saturday night and have my computer spit out a random evening set-list, usually 25-35 songs for a 3 hour gig.

I'm not very savvy with the numbers program......I may try this though and see what happens. Thanks !

Nov 15, 2017 11:44 AM

Reply Helpful

Nov 15, 2017 11:57 AM in response to barrythorpe In response to barrythorpe

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

Nov 15, 2017 11:57 AM

Reply Helpful
Question marked as Helpful

Nov 15, 2017 6:53 PM in response to barrythorpe In response to barrythorpe

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

Nov 15, 2017 6:53 PM

Reply Helpful (1)
Question marked as Helpful

Nov 16, 2017 4:16 AM in response to barrythorpe In response to barrythorpe

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

Nov 16, 2017 4:16 AM

Reply Helpful (1)

Nov 16, 2017 4:16 AM in response to Barry In response to Barry

Yes ! I wouldn't rely on the list to be "in Order", I always make that judgement based on the audience, the venue, the situation. The list would just help me to remember to not leave out good material, or over play stuff that is more recently picked up. Been playing "out" since I was 19 ( 62 now) so have a LOT of material. Thought this would be fun way to generate lists to pick from. Thanks !


Barry T

Nov 16, 2017 4:16 AM

Reply Helpful

Nov 16, 2017 6:00 AM in response to barrythorpe In response to barrythorpe

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:

User uploaded file


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:

User uploaded file


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

User uploaded file


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:

User uploaded file


Now select script from Numbers Script menu:

User uploaded file


Now select how many entries you want from the list:

User uploaded file


Final result:

User uploaded file


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.

Nov 16, 2017 6:00 AM

Reply Helpful

Nov 16, 2017 11:38 PM in response to barrythorpe In response to barrythorpe

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:

User uploaded file

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:

User uploaded file

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

User uploaded file

(Note the change in the values in column C)

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

User uploaded file


Regards,

Barry

Nov 16, 2017 11:38 PM

Reply Helpful

Nov 17, 2017 3:34 AM in response to Barry In response to 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

Nov 17, 2017 3:34 AM

Reply Helpful
User profile for user: barrythorpe

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