Insert text if not present in specific columns

In columns E through L I have a series of names, each one different than the last. In column M I want to insert a name based on a predetermined list but only if that name is not present in the cells E through L and if the cell in column M is blank. If M2 is populated then it should move to M3, and so on. I purposely left column M blank in my example to illustrate that the column will be blank when starting out.

E	F	G	H	I	J	K	L	M
Bob	Dan	Denise	Lenora	Michael	Lydia	Nikk	John
Nikk	Denise	Dan		Lydia	Mercedes	Michael	Ted
Bob	Dan	Denise	Lenora	Michael	Lydia	Nikki	Bill
Nikk	Denise	Dan		Lydia	Mercedes	Michael	John
Bob	Dan	Denise	Lenora	Michael	Lydia	Nikki	Ted
Nikk	Denise	Dan		Lydia	Mercedes	Michael	Bill
Nikk	Denise	Dan		Lydia	Mercedes	Michael	John
Nikk	Denise	Dan		Lydia	Mercedes	Michael	Ted

Any thoughts on where I could being?

Let's see if this works. I have attached the sample file based on the reply. Apologies for not including it initially.

If anything needs to be explained further please let me know. I did attempt to indicate what I am looking for on Column N of the 'Ideal Outcome' tab.

Try this array formula

Please Login or Register  to view this content.

You could try this if you're looking for a macro solution.

Please Login or Register  to view this content.

I tried using your array but I get an error. It looks like a comma is out of place or the formula is not written correctly. I also copied and pasted as you provided but no dice.

Originally Posted by jolivanes

You could try this if you're looking for a macro solution.

Please Login or Register  to view this content.

Looks like this works fairly well.

Do you know if there is a way to have the macro only allow three (3) of the same name? For example, Bob should only appear 3 times, same as other names.

Originally Posted by klturi421

I tried using your array but I get an error. It looks like a comma is out of place or the formula is not written correctly. I also copied and pasted as you provided but no dice.

See attachment.
Besides, each name only appear 3 times in columns A through H or only column H?

Please don't quote whole posts. Just a bunch of not needed clutter.
Refer to post numbers if required.

Please Login or Register  to view this content.

I apologize for quoting entire posts, wont do that again!

I attempted running the macro on a larger set of data but the full list of names did not run and some of the ones that were entered were only entered once. When I ran it on the smaller and original set of data I provided it worked perfectly.

I have re-uploaded a second iteration with the macro included and linked to a button to show the results when it runs.

Try

Please Login or Register  to view this content.

How do you determine what is right or wrong if as many as 7 out of 8 names are missing in a Row. Which missing name is the "right" one?

The only way that I think I can properly explain that piece is to split some of the "rules" up.

1. If a name is not present in the first 7 cells then it should enter the first available name based on the list of available names. Example, if Bob, Dan, and Denise are not present in the first 7 cells then "Bob" would be selected first as he is first, alphabetically speaking.

2. If a name is not present in the first 7 cells and has not yet exceeded the 3 name limit then the next available name should be entered. Example. Bob and Dan both have had their names entered 3 times where as Denise's name has only been entered twice, she should be entered next.

Oops, of course..

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

That works so far! I will test it out a few times and report back but so far so good.

Thanks