sort list of players by team from player list on separate sheet

[Guest]

Hello,
I have a sheet (sheet1) listing team members and other info including the
player number in column A and the team number in column G. This sheet is
sorted in order of player number, (i.e., column A).
I have a second sheet (sheet2) that shows the teams seperately and which
players are in them. Currently, I have to manually enter the team members
numbers again on sheet2 (column A). (I have some lookup functions to get the
other member info once the member number is filled it, so most of it is
automatic.) I would like my sheet2 to be smarter and automacally fill in the
member numbers from sheet1 as well. Above each team on sheet2 is a header
cell that indicates the team number.
Basically, I want to match the cell on sheet2 which contains the team number
to the team numbers in column G on sheet1 (except there will be more than one
match, so it won't be a simple match), then bring the team members over to
sheet2 that are in the corrosponding team, and avoid duplications in the
process.

thanks in advance for any ideas...
Robert

 

[Biff]

Hi!

Shouldn't be too difficult but without seeing a sample layout it's hard to
be very specific. From your description this should be the basic structure
of a formula that will do what you want.

=INDEX(player number range,SMALL(IF(team number range=header cell that
indicates the team number,ROW($1:the number of rows in the player number
range),ROW(1:1)))

This is an array formula and needs to be entered using the key combo of
CTRL,SHIFT,ENTER.

Drag copying this formula down will return all the players numbers.

Biff