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.
-
11. Nah, I want, for example, Cell C1 to show a random Item that has no Order value. The blanks will get filled in manually as Items are addressed, but the list is massive and picking an Item at human-random is seeing some near the bottom largely ignored. 2. The Order field relates to the order in which the Item was addressed, so in the example, Item 18 was worked first, Item 9 2nd, etc. There's no real limit to how many will be on the list, but it will probably be fewer than a thousand. The name are all fuzzed here, but each Item has a specific name on the real sheet. – Nick Aug 20 '20 at 19:01
-
Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén♦ Aug 21 '20 at 0:42
Add a comment
|
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.
