# How to pick a random row that also does not contain text in one cell?

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 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. 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. 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. May 6 at 2:26