Data validation that removes names but there are multiple of the same name

Hello everyone, I was wondering if there was a solution to this problem:

I want to have a data validation list that removes names that are already used, the caveat being that it's an employees list and there can obviously be multiple people with the same first or last name. So if there are, for example, four "Bob"s, I'd like to have the name appear in the dropdown until it is selected four times, or two last names of "Williams" so it should be selectable twice, and then is no longer selectable if that makes sense. Thanks for any help.

Sample.xlsx

Please see the updated sample file. This is the way that I would do it.

Please change the filter to

Formula:

Please Login or Register  to view this content.

otherwise you will get a #CALC! error when all the names have been allocated.

Updated sample file.

You're welcome.

It seems odd to me that you can mix and match first names and last names but, if that's what you want to do, go for it.

The solution I provided still kept the first and last names separate, but combined them for the purpose of selection.


Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

You're welcome. Thanks for the rep.

Strange that your boss wants the ability to roster Bob Sinatra and Frank Marley. Totally illogical. Even if wrong pairings are unintentional, the potential for human error, confusion and embarrassment is immense. Not sure why he would want that when it is avoidable. But guess he's paid the big bucks to make the stupid decisions.

Good luck.