pick names at random

You can do random excluding x values with formulas. Give me a moment and ill post something (have to dig it up).

Here is my sample file with randbetween excluding x numbers. Its got plenty of comments/explanation along with it for those who care to learn.

This doesnt directly address the OP's question, which is a bit confusing as they are asking for multiple things that seem to conflict. I need to clarify some things for myself to be able to help.

Please explain your needs as bullet points, it may help us better understand each goal. As I understand it now:

• You want to randomly pick a name (you never said from which list or into which column)
• You want the random selection to eliminate previous selections as an option for following random choices
• You want people to make a selection and limit that selection to a max of 2 total?

The confusing part of this is you cannot randomly chose AND have people pick. You havent been clear about where the choice is made and where the random selection happens. They CANT be in the same place, thats like saying its cold and hot at the same time, hence peoples confusion.

Please explain your goals more clearly. If it helps to explain, comment your sample file to point us to what you are mentioning. When you explain use references in the file so we can follow (IE: "I have this formula =formulax" doesnt help us, "I have formulax in cell A2, filled down to A10..." helps us follow what you describe).

To prevent more than 2 of the same entry in Column B use this formula in a custom data validation rule in B2

Formula:

Please Login or Register  to view this content.

Highlight B2:B11 (or whatever the last row in B is you need to check) and click data validation, it will ask if you want to expand the selection, say yes, then hit ok on the validation window to close it. Any existing duplicates beyond 2 entries will remain, any new entries that result in more than 2 duplicates in B will fail and not allow the entry with a warning dialog (customize it via data validation rule).

Also you say your random works. What does it need to exclude from (only column C random picks, or also the entries in column B)?

For example should the 4th row be allowed to do this (Thomas in B and C):

cap13213.PNG

EDIT: I also noticed from your random pick that column A can = column C. In its current rendition you could end up with the same name in all 3 columns (which I presume isnt valid either). So you can end up with A and B being the same, A and C being the same, B and C being the same or A,B,C all being the same. Is that ok?

Hi,
I tried the rule to identify duplicate more than two entry in column B. but not working. can you help me to insert in the spreadsheet. also the random in column C should pick from Colum A. why the random pick in column C is always changing?
yes you were right, the random pick should should validate the column A and B. do not duplicate the same name in Column C

I will post a sample in a bit with the data validation. (attached has data validation in it working as far as I can tell)

So if I am understanding correct the random name should follow these rules:

• Be sourced from the names in Col A
• Never be the same as the name in Col A on the same row
• Never be the same as the name in Col B on the same row
• Never be the same as any previous randomly generated names in Col C

I can attempt this, but my gut tells me (without doing the math) that that combination opens the possibility of exhausting all options for a random name towards the bottom of the list. IE: lets say "John" is in A2, was only used 1 time in B and hasn't been randomly chosen yet in C. We are on the last row, A <> John, B is John (2nd time) and for C the only name not previously randomly selected is John. We are now in a hypothetical situation in which we cant randomly pick the only name left. What would you want to do if that happens?

I knew this going in, but my random exclude x formula does not scale well (as you need to add additional not sets to it for each value to exclude). This is ok for a small handful but I would say 10 or more values to exclude becomes an issue. I am currently searching for a way to do an array vs array exclude (in which the range of values and exclude range can be different sizes) which may not be possible. At some point I may give up trying with a formula and consider using a macro to accomplish the random generation.

EDIT: unfortunately I dont have the time to continue researching this. You could adapt my formula as is to the sample, but any more rows and it will not be feasible. If someone can do an array to array comparison instead (something like A2:A10<>B2:B5) and get it to return an array of the values from A that <> any value in B, then it will be easy to adapt my function for randomizing names. It may be that VBA is easier to derive a solution from than a formula in this case.

No not in a formula. To elaborate its not only closing and reopening (presuming you have auto calculation on), its any time you make a change to the files contents (changing any cell value) and/or recalc the sheet (F9) The only way to prevent it would to be removing the formulas by pasting the results over them, in which case everything is then static and no longer randomly does anything ever again unless you enter the formulas back in.

Otherwise, if you want to generate stuff "on demand" then you need a macro. Press a button, run the macro, generate your desired output, place it in cells...rinse/repeat as need be.