pull names into individual lists

Question

I can't figure out the formula to get the names from the trainee list to populate into their assigned team.

Answer 1

Here is a screenshot of the actual sheet I am working with. I tried the formula you suggested but this was the result... I did modify the formula so it had the correct reference ranges. Any additional guidance anyone can offer would be much appreciated. Ideally I would like to be able to just copy and paste the list of trainee names from a separate document into the yellow Trainee column and then have those auto sort into the number of teams I designate in the "# of Teams" column.

Answer 2

As per my screen shot give this formula at D2 and copy it right side.

=FILTER($A$2:$A$6,""&$B$2:$B$6=TEXTAFTER(D1," "))

Answer 3

Name	Group
John	1
Jane	3
Sam	2
Sara	4
Pete	1

Group 1	Group 2	Group 3	Group 4
John	Sam	Jane	Sara
Pete

I already have the groups to random assign next to the names but I need the names to populate under the group in the second table based on the random group assignment from the first table. I should also let you know that the number of names in table 1 can change and the formula I used to assign the group changes with each name added. The number of groups also changes based on the number of leaders available to run the groups. So i have it set to where if i put that i have 3 leaders in a separate part of the spread sheet it will randomly assign numbers 1-3 same for 6 it would be 1-6. But I have set colums for the max number of groups which is 6. So basically if the groups are only 1-3 I only want groups 1 - 3 on table 2 to populate with the names but if I change it to 6 groups the next time I want all 6 columns to populate with names. I'm also open to any other shortcuts you can think of! I just need a list of names to separate into a number of colums that I designate and can be easily changed without altering the formula every time i need it. I hope that makes sense!