choose random numbers from list excluding some, or automatically refresh formula until results ok

[Cheeks1969]

Hi,
I have a list of 20 values in excel. I want to pick four by a certain rule, which I have figured out. Now I want to pick 6 randomly from the remaining 16.
I used 'randbetween' for the whole range of values (since I won't know ahead of time which 4 will be picked by the rules) along with a "duplicate?" function that says 'True' if any of the randomly chosen values match another randomly chosen value or one of the four chosen by the rules.

I'm curious if someone knows how to automatically refresh (f9- generate new random numbers) until they are all unique values - 'False' on the duplicate checker. Is there a way to do this with formulas only (no macros or arrays) ???

Alternatively, is there a way to choose randomly from a list while excluding certain cells that are previously chosen, but not known ahead of time, or what place they will be in the list?

Thanks in advance for any replies!

 

[Mats80]

Sure:

=LET(a,FILTER(B2:B21,COUNTIF(A2:A10,B2:B21)=0),SORT(INDEX(SORTBY(a,RANDARRAY(ROWS(a))),SEQUENCE(D2))))

Awesome Eric. Thank You.
Note: You would definitely excel in creating some youtube videos in this field.

 

[Cheeks1969]

I was surprised to see this thread come back to life. It took me a while to figure out why I asked this question but finally remembered and thought I would share it here.

I was working for a flood insurance company and wanted to use the coordinates from the perimeter of a building to find the most flood-vulnerable spot (using those coordinates to generate a flood risk score from a vendor), and also use some random spots from the remaining coordinates to generate the flood risk scores. The maxium score was identified and the coordinates that generated it were used to identify the spot. I would have then chosen from the rest randomly, but never finished because I believe we went another route. The point of choosing randomly would have been to see if any other spots generated a higher flood risk score, but 90% of the time it was a corner or the furthest point in one direction.