0

I'm making a roller that randomly picks from one or two lists to assemble a new vertical list. I'm trying to prevent a specific few values from appearing together in one assembled list. These values, for reference, are W-2, W-1, W-0, W+1, W+2, and W+3. There should only ever be one of these in a list. For an example, here's something that my roller may generate:

Tn  
AA  
En  
SpM  
W-1  
Sp-2  
W-2  
Co  

The problem here is that W-1 and W-2 are on this same list. Ideally every "W" cell except for one would be made blank or prevented from happening to begin with. Whether the one remainder is random or the first/last one doesn't matter (if I can be specific though, the ideal would be for the highest number to be left. So between W-1 and W+2, W-1 would be removed. However I'll gladly accept the simplest route whatever it may be.)

Here's a copy of my roller.

This is on the Data sheet.

1

There might be a easier way to do this, but, Here's a solution:
Add a extra column after BH

BI2:

=ARRAYFORMULA(ARRAY_CONSTRAIN(SORT(BH2:BH27,--REGEXREPLACE(BH2:BH27,"W(.*)", "$1"),0),COUNTA(BH2:BH27)-SUM(--REGEXMATCH(BH2:BH27,"^W"))+1,1))

We're sorting by the W in the descending order and constraining it to the first result only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.