so I have a google spreadsheet full of YouTube videos. Column A looks if the video is already in column B, and if it is it says "duplicate"

Column B is YouTube link

Column C is the title of the video

Column D is the name of the author

Column E is the genre (eg. car review, lets play, comedy...)

Column F is the length

And finally column G contains "x" as in I already watched it. And few times "unlisted".

So I want to create a randomizer which picks a random link and all the info about it, but does not contain "x" - random video that I haven't watched.

I came up with this =INDEX(B5:G; RANDBETWEEN(1; COUNTA(B5:B))). Works great as a randomizer, but it doesn't care about the filter I have set.

If someone has a idea how to pick a random link which does not contain a text in column G and lists the info - title, author, genre etc.., I would be very happy.


Try this:

=sortn(B5:G, 1, 0, not(isblank(B5:B) + (G5:G = "x")) * randarray(rows(G5:G)), false)

The benefit of using sortn() is that you can easily get more than one result if needed. For example, you can use 3 instead of 1 in the second parameter to get three results.

Note that the formula will start returning rows with "x" when you run out of rows where column G is something other than "x". If that is an issue, wrap the formula in a query() to only include rows where Col6 <> 'x'.

  • Oh wow, this is working perfectly ! I was actually thinking about it randomly selecting 3-5 videos. Thank you so much ! ... after some testing I ran into a problem with these unlisted videos, so with some trial and error I came up with this =query(sortn(B5:G; 5; 0; is.blank(G5:G) * randarray(rows(G5:G)); false);"where Col6 <> 'x'") is that a viable option or is my solution flawed ? seems to be working tho. May 3 at 20:12
  • Looks good! I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? May 3 at 21:23
  • @JamesGreen You mentioned I ran into a problem with these unlisted videos - would you care to describe the problem? Based on my test data, your formula returned only 4 visible rows; I suspect that it also returned one non-visible row to total 5 rows in all. No doubt the following could be simplified but I used =sortn(query({B5:G},"select Col1, Col2, Col3, Col4, Col5, Col6 where (Col1 is not null and Col6<>'x')"), 5, 0, randarray(rows(query({B5:G},"select Col1, Col2, Col3, Col4, Col5, Col6 where (Col1 is not null and Col6<>'x')"))), false) and it returns five visible rows.
    – Tedinoz
    May 6 at 2:51

Try this: =index(filter(B5:G,G5:G<>"x",B5:B<>""),RANDBETWEEN(1,ROWS(filter(B5:G,G5:G<>"x",B5:B<>""))))

Your formula has a basic problem: COUNTA(B5:B) returns the total number of items in the column regardless of whether all the rows relate to spreadsheet and of whether a given row has been watched or not.

Instead =ROWS(FILTER(...)) will return the number of rows returned by the filter, and thus give an upper value for randomising a row.

The FILTER function is used to return the array of unwatched links. The function requires two criteria:

  • G5:G<>"x" - eliminating watched links.
  • B5:B<>"" - defining the array by only those cells in Column B that aren't blank.
  • Hello, thanks for your answer! I put your formula in, and it worked ! sometimes it showed #REF! that index number is too high. I tried to fiddle around with it, and made this : =index(filter(B5:G;G5:G<>"x");RANDBETWEEN(1;COUNTA(B5:B))). Now it doesnt show #REF! and it seems that it works. May 3 at 12:16
  • Both COUNTA(filter(B5:G,G5:G<>"x")) and COUNTA(B5:B) get an upper boundary that is too high. These formulas will often return a blank result when there are many blank rows at the bottom of the sheet. May 3 at 13:05
  • @JamesGreen Sorry, there was an error in the formula as user doubleunary pointed out. I've revised the answer and you find that it returns one unwatched link.
    – Tedinoz
    May 6 at 2:23
  • @doubleunary Quite right and thank you. My bad, I failed to populate Columns C-E in my test data. Also, the filter was faulty - returning all of the blank rows in the spreadsheet. Answer has been revised. Funnily enough, I hadn't noticed your use of ROWS and I found this reference. But kudos to you for getting it right.
    – Tedinoz
    May 6 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.