Log in

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


Orestees
01-23-2013, 12:44 AM
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.

571

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b (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=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o (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=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
mrmmickle1
01-23-2013, 04:35 AM
Orestees,

Try something like this:

=IF(ISNUMBER(SEARCH("*Tinker*",B3)),B24,IF(ISNUMBER(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.
mrmmickle1
01-23-2013, 05:22 AM
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
Orestees
01-23-2013, 09:58 PM
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!
mrmmickle1
01-23-2013, 10:06 PM
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.
Orestees
01-23-2013, 10:25 PM
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! :)
Orestees
01-24-2013, 01:31 AM
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.
mrmmickle1
01-24-2013, 04:41 AM
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")))))
mrmmickle1
01-24-2013, 04:44 AM
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.
Orestees
01-24-2013, 05:13 AM
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!
mrmmickle1
01-24-2013, 06:08 AM
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.
Orestees
01-24-2013, 08:56 PM
Very true, lol. Thanks for the heads up!
Orestees
01-26-2013, 12:24 AM
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!
snb
01-26-2013, 03:47 AM
Please do not use merged cells.
Structuring precedes coding.
Orestees
01-26-2013, 04:58 AM
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!