I have a list of hundreds of values organized as follows:
I'd like to have a cell generate a random Item for which there is no corresponding Order value.
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityI have a list of hundreds of values organized as follows:
I'd like to have a cell generate a random Item for which there is no corresponding Order value.
See Test sheet
Formula in C1 returns random value from list of items with no order number
=INDEX(
FILTER(A:A,B:B<>""),
RANDBETWEEN(1,COUNTA(FILTER(A:A,B:B<>""))))
FILTER() part filters items with no order number.
COUNTA() - counts number of items in filtered list.
RANDBETWEEN() - generates random value from 1 to number of items in filtered list.
INDEX() - returns that random item.