How to select a random item in list not flagged in second column?

My suggestion would be to add a column with a random number. Then sort the whole thing on the random number. Watch the movies in the order given. Once you've watched them all, regenerate and re-sort the list.

    239 Movie A
    475 Movie B
    683 Movie C
    241 Movie D
    748 Movie E

After sorting, (in Excel--I assume OOCalc would do the same) the random numbers get regenerated:

    145 Movie A
    978 Movie D
    783 Movie B
    322 Movie C
    773 Movie E

The random numbers also get regenerated when you close and reopen the file (again, at least in Excel), so don't sort again until you've gone completely down the list.

If you really want to get elaborate, for example, if you also want to make sure you do not watch the same movie twice within a 30 day period (I.E., avoid the random possibility that the last movie of one sort becomes the first movie of the next sort), you can use a column as a timestamp for when you last watched it, then simply add 30 plus some random number, and then sort. You could even add a second random number column to insure more randomness in the occasional time the same random number gets generated for more than one movie.

The variations and possibilities are virtually endless.

C. M.

Posted 2014-07-19T15:18:36.120

Reputation: 687

While, I do like this, but I'm thinking an If statement could work where I use my code, then use an If It's True I haven't watched the films, then, It stays, otherwise It tries again. – Josh Staats – 2014-07-19T16:24:41.027

Basucally I'm wondering how to get this to work correctly.

`=INDIRECT("B"&RANDBETWEEN(1;COUNTA(B2:B178)))IF(C2:C178; ;=INDIRECT("B"&RANDBETWEEN(1;COUNTA(B2:B159))))`
 – Josh Staats  – 2014-07-19T17:27:11.110

There problem with that approach is that you do not want to watch the same movie twice. Here's an example situation--say you've got 5000 movie titles. Using your function, you've watched half. Now, every time you want to watch a new one, your function will return a movie you've already watched half the time, so it needs to pick again. Once you've watched 3/4, it'll return one you've watched 3/4's the time. By the time you've watch 4999.. your function would have to loop around, on statistical average, 4999 times to find that one movie you've not watched! – C. M. – 2014-07-19T23:24:46.953

Thus, what you really want to do isn't to pick a movie at random--You want to shuffle the list of movies to watch. It's like shuffling a desk of cards by removing a card at random--To insure you don't pick the same card again, you remove it from the deck and put it aside, which makes the stack of remaining cards smaller, until it's gone. When it's gone, that stack of cards you pulled is in some random order. But if you put the card back in the deck, you risk the chance that you'll keep randomly pulling the same card, over and over again. – C. M. – 2014-07-19T23:28:08.560

While you can use the idea of.. "Pick a movie at random, if it's been watched, move down the list from that point until you find an unwatched movie", you'll also have to code in how to check if the movie has been watched, and how to handle it if you reach the end of the list without finding a movie. These are things you'd have to write full VBA code for, not use a simple Excel spreadsheet function, because you need to maintain "state information" while it's running to find a suitable candidate to watch. – C. M. – 2014-07-19T23:36:09.260

What's the question? – a CVn – 2014-07-19T15:19:30.560

So I have a movie list, and everytime I watch one I have another column for me to mark if I have watched it or not, marked with a yes, otherwise left blank, and with my code, it randomly picks a movie, but I need it to randomly pick a movie I haven't watched. – Josh Staats – 2014-07-19T15:23:19.663

How to select a random item in list not flagged in second column?

Answers