Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0

    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.

    Schedule Demo.xlsx

    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
    Last edited by DocAElstein; 02-24-2024 at 08:33 PM.

  2. #2
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    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.
    Last edited by mrmmickle1; 01-23-2013 at 04:46 AM.
    Using Excel 2010

  3. #3
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13

    I think I was trying to make it too complicated

    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
    Attached Files Attached Files
    Last edited by mrmmickle1; 01-23-2013 at 05:35 AM.
    Using Excel 2010

  4. #4
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0
    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!

  5. #5
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    Quote Originally Posted by Orestees View Post
    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.
    Using Excel 2010

  6. #6
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0
    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!

  7. #7
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0
    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.

  8. #8
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    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")))))
    Using Excel 2010

  9. #9
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    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.
    Attached Files Attached Files
    Using Excel 2010

  10. #10
    Junior Member
    Join Date
    Aug 2012
    Posts
    13
    Rep Power
    0
    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!

Similar Threads

  1. Replies: 1
    Last Post: 05-09-2013, 08:56 AM
  2. Replies: 2
    Last Post: 04-10-2013, 12:40 AM
  3. Replies: 2
    Last Post: 03-21-2013, 10:38 PM
  4. Converge Data From Multiple Columns To Single Column
    By ayazgreat in forum Excel Help
    Replies: 3
    Last Post: 12-14-2012, 10:55 PM
  5. Replies: 4
    Last Post: 05-14-2012, 11:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •