CODEWITHSUNDEEP
randomgoogle-sheetsexcel-formulaformulasgs-vlookup
Fiztban
Fiztban

Reputation: 275

How to randomly select from a list of rows excluding rows with certain values in adjacent column

I am trying to make a random generator which picks random cards from a list of cards (from a game) but I would like to have the ability to enter a few parameters to filter out certain subsets of cards. Lets say every card has 2 types of subset, a <Rarity> and <Colour>.

So far I set up my generator to work with a formula on Google Sheets as follows:

     A         B         C            D             E          F         G
1  Card    <Rarity>   <Colour>  <Filter Rarity>    Card    <Rarity>   <Colour>
===============================================================================
2  Blah1    Common     Blue           X              <Generator formulas>
3  Blah2    Common     Red            X              <Generator formulas>
4  Blah3    Uncommon   White          X              <Generator formulas>
5  Blah4    Legendary  Green          X              <Generator formulas>

On the left most three columns are the cards with their respective details. The D column was the one I was trying to enter all the <Rarity> values I would like my formula to ignore when randomly picking from the A,B,C columns.

The code in the E,F,G columns are as follows:

E
=INDEX($A$2:$A$537,LARGE(MATCH(ROW($A$2:$A$537),ROW($A$2:$A$537))*NOT(COUNTIF($E$1:E1,$A$2:$A$537)),RANDBETWEEN(1,ROWS($A$2:$A$537)-ROW(A1)+1)))

F and G just vlookups to return the values in columns B and C.
=vlookup(E2,$A$2:$C$760,2,FALSE)

I pieced this formula together from other formulas I had done previously so I had to go back and try to figure out how to enter a condition that if any of the cards in A have a value in B that matches one listed in D then they should not be picked. But I am not sure where best to put it or how.

Upvotes: 0

Views: 3043

Answers (1)

AdamL
AdamL

Reputation: 24609

One way:

=QUERY(FILTER({A2:C537,RANDBETWEEN(SIGN(ROW(A2:A537)),1E+308)},ISNA(MATCH(B2:B537,D2:D,0))),"select Col1, Col2, Col3 where Col1 != '' order by Col4 limit 10",0)

This appends a column of random numbers, filters out the ones that don't appear in column D, sorts the first 3 columns based on the column of random numbers, and "limit 10" allows you to specify how many cards you want randomly selected.

Upvotes: 2

Related Questions