Oct 28 2021 07:37 AM
I'm trying to create a book randomizer to choose a random book to read, but then to exclude it from the options if it's been read before.
I have a list of Books in Literature!A:A and in Literature!B:B =COUNTIF('2021'!D:D,A4) to check if it's been read already this year from '2021'!D:D.
I would like to modify this formula below to exclude books that have a value of 1 in the Checking column.
=INDEX(Literature!A1:A100,RANDBETWEEN(1,COUNTA(Literature!A1:A100)))
Thank you for your help!
Oct 29 2021 04:07 AM
For such layout
formula could be
=LET(
N, ROWS(allBooks[ID]),
k, SEQUENCE(N),
array, RANDARRAY(N,1,1,N,0),
random, SORTBY(k, array),
bookID, @FILTER(random, NOT(XLOOKUP( INDEX(random,k), allBooks[ID], allBooks[Read]) )),
INDEX(allBooks[Book], bookID) )