Google Sheets Select Number From List Excluding Another List

Hi all, hope this is ok in the Excel forum, I did see a lot of google sheets questions but could not find a spcidic google sheets forum on this site.

We have a google sheet where we randomly select some numbers from a list in one column, A3 to A143, and would like to now not include a subsection of those numbers in our group, but without deleting them. That does work of course, but its not ideal. Is there an addition to the formula below that will then not select any numbers that are also in another column, maybe on another sheet.? I have attached a sample sheet, hope thats ok too.

=ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(A3:A143,RANDBETWEEN(1,ROWS(A3:A143)),1)), 1, 1)

Thanks all

Hi Willem

Thank you for your reply. Yes, I see what you have done,
but that is just the same as deleting from the A column.
What I need to do, is select from the A column, all of it,
from A3 to A143, but not choose a number that is listed in
another column, for instance B3 to B75, in your example.
A3 to A143 has to be all the numbers listed, 101 to 241,
but the rand selector should not choose any number that is
also listed in the B3 to B75 range.

I hope I have explained it more clearly.

Thanks again for getting involved

How about

Formula:

Please Login or Register  to view this content.

Hi Fluff,

Thanks for your help. That does work, but when I edit the banned numbers column to add some numbers, and change the formula to include those cells, ie the below :
=sortn(FILTER(A3:A143,ISNA(MATCH(A3:A143,'Banned-numbers'!A3:A35,0))),1,,RANDARRAY(rows(A3:A143)-rows('Banned-numbers'!A3:A35)+1),1)
...it then comes back with an error 'SORTN has mismatched range sizes. Expected row count: 118, column count: 1. Actual row count: 116, column count: 1.'.

Any ideas why that is, and what that refers to.?

Ok, you may have some duplicate numbers in the banned list, try it like

Formula:

Please Login or Register  to view this content.

Thats the one Mr Fluff.! Wow, thats amazing. Thank you very much for this

I dont suppose there is a way to add the number selected to the end of the list in banned numbers as well is there.? A function or macro to run with a key press.?

Thanks again, you deserve the title of guru

There is https://www.excelforum.com/for-other...mobile-os-etc/, Forum: For Other Platforms(Mac, Google Docs, Mobile OS etc).

You could name the range in the Banned-numbers worksheet banned, then try

Formula:

Please Login or Register  to view this content.

That should work as long as you keep banned defined correctly to span all the banned numbers in Banned-numbers!A3:A.

Why sort? A random numeric index into filter's result would be more efficient.

Is your end goal exhaustive random sampling without replacement? If so,

Formula:

Please Login or Register  to view this content.

would produce a shuffled listing of A3:A143.