Produce Random numbers from a list with exceptions

And here is an AppleScript solution that prevents repetition of lesson pairs:

Usage:

1. Copy-paste script into Script Editor (in Applications > Utility folder).
2. Click the triangle 'run' button.
3. Click once in a target Numbers cell (in the posted example cell A3 of Πίνακας 1 on the 'Lessons - extra table for pasting sheet')
4. From the menu in Numbers choose Edit > Paste and Match Style.

Here's what it does. First. it makes a list of all possible pairs of lessons. Then it makes a random selection from the list and adds that selection to a new list if it's not already in the new list and does not contain a lesson that is supposed to be skipped for that day. Then it makes another random selection, and repeats the process. Once it has assembled the new list it checks to make sure each lesson is read enough times. If not it starts over. The number of lessons, number of days, days to exclude, etc. can be changed in the statements at the start of the script.

SG

set numDiffLessons to 8

set numLessonDays to 30

set minTimesRead to 5

set excludeList to {{1, 5}, {1, 10}, {2, 1}, {2, 3}, {5, 22}, {6, 23}, {6, 24}}

-- enter as {{lesson,day to exclude it},{lesson,day to exclude it}}

-- make list of all possible pairs

set possiblePairs to {}

repeat with i from 1 to numDiffLessons

repeat with j from 1 to numDiffLessons

if i ≠ j then ¬

set end of possiblePairs to (i & j)

(* or if want to lesson i,j to be treated as repetition of j,i then use this instead:

if i ≠ j and {j & i} is not in possiblePairs then ¬

set end of possiblePairs to (i & j)

*)

end repeat

end repeat

--warn and halt if not enough pairs to fill schedule

if (countpossiblePairs) < numLessonDays then ¬

display dialog "Not enough lesson pairs to fill schedule without repeating" buttons "Cancel"

set minFreqOK to false

set loopCtr to 0

repeat until minFreqOK

set loopCtr to loopCtr + 1

if loopCtr > 100 then display dialog "No solution. Try again?" buttons "Cancel"

set selectedPairs to {}

repeat until (countselectedPairs) = numLessonDays

set dayCounter to (countselectedPairs) + 1

-- select a random pair from list of all possible pairs

set randomSelection to random numberfrom 1 tocountpossiblePairs

set thisPair to possiblePairs'sitemrandomSelection

-- is it already selected?

set alreadySelected to {thisPair} is in selectedPairs

-- is it on exclude list?

set onExcludeList to checkExcludeList(excludeList, thisPair, dayCounter)

-- add to list if not already selected and not on exclude list for that day

if not alreadySelected and not onExcludeList then ¬

copy thisPair to end of selectedPairs

end repeat

-- after list is complete, check if minimums are met

set minFreqOK to checkFreq(selectedPairs, numDiffLessons, minTimesRead)

end repeat

set the clipboard tomakeTabbedStr(selectedPairs)

return the clipboard

to checkExcludeList(excludeList, aPair, dayCounter)

--check if either member of pair is on the exclude list for that day

repeat with i in aPair

if getValue(excludeList, i'scontents) = dayCounter then return true

end repeat

return false

end checkExcludeList

to getValue(keyValueList, findKey)

-- get value associated with a key-- similar to LOOKUP

repeat with i in keyValueList

set {thisKey, thisValue} to i

if thisKey = findKey then return thisValue

end repeat

return "" -- return null if no match

end getValue

to checkFreq(pairsSelected, numLessons, minFreq)

-- checks that each lesson is read the minimum number of times

repeat with i from 1 to numLessons

set n to 0

repeat with j from 1 to count pairsSelected

if (pairsSelected's item j as string) contains i then ¬

set n to n + 1

end repeat

if n < minFreq then return false

end repeat

return true

end checkFreq

to makeTabbedStr(aListofLists)

--converts to tab-delimited data for pasting to Numbers

set tabbedStr to ""

set AppleScript'stext item delimiters to tab

repeat with i from 1 to count aListofLists

set tabbedStr to (tabbedStr & i & tab & aListofLists'sitemi as string) & return

end repeat

set AppleScript'stext item delimiters to ""

return tabbedStr

end makeTabbedStr

Germanos wrote:

The point is that I want 10 (or a number of) random numbers, not to produce exactly the same list!

It doesn't produce exactly the same list. Note, for example, the new list does not include 12. Have you actually tried either of the approaches described? 🙂

You wrote that you wanted to extract 10 different random numbers from the A list (emphasis yours). So if you have a long list in A (longer than the short example in my screenshot) you can extract 10 random numbers from that list using the approaches shown. Each time (after making a change to the document to retrigger the RAND() function in E) you resort the table on E and get a new list of 10 random numbers from the numbers that are contained in column A. Isn't that what you want?

If, after trying the examples, you are looking for something different from what you described, then just post a clarification and we'll attempt to provide a solution.

SG

Germanos wrote:

Hello again SGII and thanks for your help.

If you see your random list you will notice that the random numbers is the first 10 of the wanted list. Not randomly produced e.g. first one the 38 , second the 9 etc...This is the main problem 🙂

Hi. Note that I have a column of random numbers in column D that is regenerated each time a change is made to the document. When you sort on column D after those random numbers are regenerated in D that you get a different set of first 10 numbers. That gives you a new set of random numbers in the first 10 each time you resort the table. Have you given it a try? It does work. And you can exclude numbers as you want.

SG

Take a look at this variation of a file I previously shared for making random lists:

https://www.dropbox.com/s/a296hf6hhzox3ns/RandomLists.numbers?dl=0

The original list is here:

https://www.dropbox.com/s/pvtxcmt5a76wxty/RandomNoDups.numbers?dl=0

Germanos wrote:

But the real problem is far from this:

On rereading your latest post, I see that it seems to be an entirely different problem from your original post. You want to make 2 random selections each day from 8 possibilities where the second selection must be different from the first selection. And within a month each of the 8 possibilities must be selected at least 5 times and the selection of certain possibilities cannot occur on certain days of the month. Maybe I'm misunderstanding the problem. But for that to be possible, wouldn't a month have to have at least 40 days?

SG

Edit to above: If you're selecting 2 each day, then a month is enough time; the problem seems to be how to exclude lessons from selection once they've been read "too many" times to allow others to be read the required number of times within a month.

SG

Germanos wrote:

Nice work Wayne, but how this way affect my problem?

Wayne's solution does apply to your "problem", as you originally described it. This thread seems to be a classic case of "moving the goal posts." A question is asked. Suggestions are given. The suggestions are rejected for various reasons, some clearly without even being tried to understand what they do. Then the problem is changed (the goal post is moved). And the sequence is repeated ....

Hope someone is able to help you to your satisfaction.🙂

SG

I expanded the document I posted to demonstrate three lists.

You should be able to use this example to expand to 10.

Duplicate the "List1" table and rename "Listx" where ix is the number of the table

then add columns on the table "Random List" table and name the columns "List1" thru "Listx" where x corresponds with the the number of tables

If this is not what you want, please describe what you want in a single, concise, post

Germanos wrote:

I tried your idea and (I don't know why, maybe I missed something) the result was to have the first 10 acceptable numbers of the first list (as I mentioned before). It was nice but not exactly what I wanted.

What you probably missed is the sort on column E. Sorting on column E (which contains random numbers) randomizes the order of the values in A. Then taking the first "10 numbers" is in effect a random selection from column A.

When you make a change in the document (clicking the checkbox is a convenient mechanism) the random numbers in column E regenerate. Sort by E again and you get another random set of "first 10."

(Wayne's method is another way of getting 10 random values that has the advantage of not involving sorting; I'm not sure if it excludes values.)

That may not be "exactly what you wanted" but is certainly very close to what you described as wanting.🙂 It does not reproduce the original list, and it does provide random (actually I think spreadsheet functions produce "pseudo" random) selection, with some values excluded.

Your new problem–with its added constraints such as a minimum number of times a particular selection appears in a month and the selection of 2 unique values each day–is something else entirely. Hopefully, sharper minds than mine will provide a solution.

SG

Barry wrote:

Choice::A2: =IF((ROW()-1)>$A$1,"",LOOKUP(SMALL(Source :: $D,ROW()-1),Source :: $D,Source :: $C))

LOOKUP looks for the nth smallest value in Column D of Source, and returns the matching item from column A of Source.

... returns the matching item from column C of Source which (if not ''') is the same as the matching item from column A of Source.

Looking forward eagerly to the next installment here.🙂

SG

SGIII writes:

"... returns the matching item from column C of Source which (if not ''') is the same as the matching item from column A of Source.

Looking forward eagerly to the next installment here."

Good eye! 😁 Actually, the formula could be revised to return the value from A, as it won't return a "" from C (or a value from A in a row with "" in C unless it is asked to choose more items than are included in the "new" list in C.

Germanos writes:

"I'm waiting for a solution for the new problem...it is quite difficult and I feel frustrated..."

No doubt! Me too.

Regards,

Barry

Barry wrote:

Germanos writes:

"I'm waiting for a solution for the new problem...it is quite difficult and I feel frustrated..."

No doubt! Me too.

Help us, OBa-rry-Kanobi, you're our only hope🙂 ... unless there are others out there. The force is even weaker than usual here.

SG