Need to generate list of names based on criteria

[farqual]

So i am trying to make a list that will ultimately be used to keep track of data from 5 different locations. I have generated the data fields and made a reports / totals page and all of that works so far. I am doing this for a tournament and need to divide up the competitors.

there is a tab i made called tigers that i need to query all location pages K2:N91 and give a result of names from cells corresponding to the same row using only column E-J.

Example If there is student name Parent Phone..... Division 1 Division 2 etc....
Adam Adams Bob 909 888 7777 TT Forms TT Sparring

would want it to report on the tigers sheet

TT Forms

Adam Adams Bob 909 888 7777


and i would make another area to do the same with TT Sparring and all my categories though the whole list and make new result pages for Discovery, BBTI, BBTA, Jyo, Adult.


So thats problem one, Tried a few INDEX and Vlookup functions but have been unsuccessful so far.

Next challenge will to be to sort all participants from all the lists into groups of 4 and have them sorted so that the amount of people from the same location is minimal. ie. one from Towne Lake, one from Canton, one from smyrna, and one from Dacula. and have them sort as such from the list genorated. I will then take that list and have it populate and print to name lables with what divisions they will e in as well as time and ring to go to.


https://www.dropbox.com/s/p9rwy2dniao03ij/Dragon Invitational Master List.xlsx

File is above.

 

[farqual]

Can someone confirm that the link is working?

 

[farqual]

I have tried to get =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)) to work but with no sucsess

 

[farqual]

Now tried this to reference the Canton page ,.... still no luck

=INDEX(Canton!K2:P2,SMALL(IF(Canton!K2:N2=Tigers!B1,ROW(Canton!K2:N2)),ROW(Canton!A2:A2)),2)

I cant even get it to pull the data from one sheet, how am i going to get it to populate from all locations?? im willing to try and hash it out just need help to be pointed in the right direction. I feel like im on a snipe hunt ~_~