Hoping to extract team info from a Roster and grouping teams together into a list

Hi All!

I've been staring at my roster for a while and had an idea which I'm not sure is possible in excel.

I am basically trying to extract cell infomation from the roster into another sheet to group people into teams based on the symbol they were allocated to in the roster.

I have attached the file and will break it down so please open it to make it easy to follow. There are two sheets in the workbook.

THE ROSTER
1. you can see the names on the left on each row and the dates on the right.
2. In each row each person on some days will be allocated to a RED team. E.g. P10,P11,P31 etc.

THE TEAMS (Team SHEET)
1. On the left are the team names e.g. p10,p11,p31 with the dates on the right.
2. I am hoping for all staff members who had been allocated to a team on a particular day in the roster e.g. p10, to be listed in the P10 area for that particular day in the Team sheet.

In the TEAM SHEET I have prefilled the teams P10 and P11 for 5 days to give an example of what I hope to achieve.

Is this possible in excel? Please let me know if I have not been clear.


Oh I'm also on excel 2007, excel 2010 is an option though just might take some time to get the upgrade at work -_-

b11=IFERROR(INDEX(Sheet1!$C:$C,SMALL(INDEX((Sheet1!D$6:D$50<>LOOKUP(2,1/((A$11:A11<>"")*(RIGHT(A$11:A11,4)<>"Seat")),A$11:A11))*10^10+ROW(Sheet1!D$6:D$50),0),ROW(B11)-MATCH(LOOKUP(2,1/((A$11:A11<>"")*(RIGHT(A$11:A11,4)<>"Seat")),A$11:A11),$A:$A,0)+1)),"")

=IFERROR(INDEX(Sheet1!$C:$C,SMALL(INDEX((Sheet1!D$6:D$50<>LOOKUP(2,1/((A$11:A11<>"")*(RIGHT(A$11:A11,4)<>"Seat")),A$11:A11))*10^10+ROW(Sheet1!D$6:D$50),0),ROW(B11)-MATCH(LOOKUP(2,1/((A$11:A11<>"")*(RIGHT(A$11:A11,4)<>"Seat")),A$11:A11),$A:$A,0)+1)),"")

Try this and copy across

Please reattach your excel file with desired result and let me know where do you want the result

whoops double post