Best way to cross populate multiple arrays based on data entry into a single array?

My user is looking to have a schedule created using 4 seperate arrays. The goal is to fill in information into one or two arrays and have the main array autofill based on these entries. The schedule itself is of a large group of employees and three seperate groups of trainers. Each day, a trainee is scheduled with a trainer and for a specific time. I'd love to be able to accomplish this without macros but am open to any and all help. I don't know if it'd have to be a lookup or match or an if, then statement.

I tried finding something like it online but either nobody's ever thought of it, or they were smart enough to figure it out on their own. I'm not and I'm horrible at syntax, so again, any and all help is welcomed and greatly appreciated.

Attachment 571

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Orestees,

Try something like this:

=IF(ISNUMBER(SEARCH("*Tinker*",B3)),B24,IF(ISNUMBE R(SEARCH("*Tom A*",B3)),B24,"NoTrainerAvailable"))

You would need to enter all the possible trainers...

Or possibly just a nested if statement. The reason to use the * is to search for a string.

Is this similar to what you wanted?

Try This in D3:

=IF(ISNUMBER(SEARCH("*Tinker*",D24)),$B$24, IF(ISNUMBER(SEARCH("*Tinker*", D26)), $B$26, IF(ISNUMBER(SEARCH("*Tinker*", D28)), $B$28, IF(ISNUMBER(SEARCH("*Tinker*", D30)), $B$30, IF(ISNUMBER(SEARCH("*Tinker*", D32)), $B$32, "No Trainer Available")))))

Change Tinker to Tailor for D5.... I have attached an example for reference

That's exactly what I was looking for mrmmickle1! I never would have been able to figure this out on my own. I've already modified it for the real schedule and have tested it. Thanks a million!

Quote:

---

Originally Posted by Orestees

That's exactly what I was looking for mrmmickle1! I never would have been able to figure this out on my own. I've already modified it for the real schedule and have tested it. Thanks a million!

---

You are very welcome. This excercise helped me learn some as well. I may be able to help someone I know who works for a local gym create a better scheduling system too. Up until yesterday I had never used this combination of formulas. It seems to come in quite handy.

It's perfect! It will save my user about 60% of his time spent on scheduling. I had worked on a userform method of scheduling before and found it to be visually stunning but not very effective and incredibly lacking in some places. This method won't change anything for him visually and works better than the userform method! Glad you learned something too. I've seen this type of formula elsewhere but never thought of applying it this way!
Thanks again! :)

Within this same worksheet, would there be a way to match up the times listed for the trainees as well? I assume it would be an identical formula as before, I'd just imagine that the wildcard would be the times. Maybe something that looks at the name of the Instructor in the above cell, searches for the time according to the data in the Instructor array?

Thanks in advance if you're able to take a gander.

Put this in D6 and autofill the formula to the right.

=IF(ISNUMBER(SEARCH("*Tailor*",D24)),D25, IF(ISNUMBER(SEARCH("*Tailor*", D26)), D27, IF(ISNUMBER(SEARCH("*Tailor*", D28)), D29, IF(ISNUMBER(SEARCH("*Tailor*", D30)), D31, IF(ISNUMBER(SEARCH("*Tailor*", D32)), D33, "No Trainer Available")))))

Put this in D6 and autofill the formula to the right.

=IF(ISNUMBER(SEARCH("*Tailor*",D24)),D25, IF(ISNUMBER(SEARCH("*Tailor*", D26)), D27, IF(ISNUMBER(SEARCH("*Tailor*", D28)), D29, IF(ISNUMBER(SEARCH("*Tailor*", D30)), D31, IF(ISNUMBER(SEARCH("*Tailor*", D32)), D33, "No Trainer Available")))))

I have attached an example.

Once again, you are a life saver! I think this helped me understand even more how this type of contatenation formula works. Thank you so much mrmmickle1!

Orestees,

If you have people editing the bottom array in the sheet you may want to lock all of the top cells with these formulas in them....
typically people will mess things up if you give them an opportunity. This way you can prevent having to go in and fix these formulas later.

Very true, lol. Thanks for the heads up!

User requested an adjustment. :eek: Is there a method to use this formula to return results for multiple names? If a trainee is sitting with multiple trainers, right now, the formula only returns the first occurence it finds rather than mutiple occurances. Hopefully I'm making sense, just flustered by the request and deciding I might be bright enough to give it a run. . . to no avail.

Thanks in advance for taking a look!

Please do not use merged cells.
Structuring precedes coding.

Thanks for taking a look snb, unfortunately, the user won't be able to have multiple cells with the instructors or trainees name within the b column. Visually, they're requesting that it stays identical to the uploaded sample. Would there be a way to work this formula into something with this type of flexibility?

Thanks again in advance!