Extracting data range results to seperate worksheet

[psu1999]

I have a data range with 4 columns: First Name, Last Name, Team, Age.
This range has all the teams combined and is currently 210 rows in size.

What I want to do is place each team on its own worksheet and have it so that if I add a new person to the data range, it will put that person on the correct worksheet based off of the Team column.

Could anyone point me in the right direction? If I need to add more clarification, please let me know.

Bob

 

[gardnertoo]

This can be done using array formulas. Consider the following set of team data:

Book1
	A	B	C	D
1	First Name	Last Name	Team	Age
2	Benjamin	Lestronge	Giants	24
3	Cesar	Manuelito	Giants	24
4	Letisha	Paeth	Eagles	25
5	Kelvin	Gett	Cowboys	22
6	Roy	France	Falcons	21
7	Dorsey	Chesson	Eagles	26
8	Jaqueline	Mcdivitt	Cowboys	21
9	Newton	Silbaugh	Eagles	22
10	Jennette	Vandervoort	Cowboys	26
11	Rochel	Digerolamo	Colts	21
12	Nadine	Sodergren	Eagles	22
13	Ava	Brandewie	Colts	21
14	Eleonor	Yoke	Eagles	23
15	Adela	Voetberg	Falcons	26
16	Tianna	Shuttlesworth	Cowboys	22
17	Kerry	Dominquez	Giants	26
18	Barney	Siler	Eagles	23
19	Hector	Florek	Colts	26
20	Pasquale	Croston	Falcons	26
21	Colin	Hallett	Falcons	23
22	Nisha	Foard	Falcons	22
23	Rudy	Lagos	Falcons	24
24	Max	Hutchinson	Giants	22
25
26
27
28
29
30
Sheet1

The team sheet for the Falcons could then look like this:

Book1
	A	B	C	D
1	Team	Falcons
2	Roster	6
3
4	First name	Last Name	Team	Age
5	Roy	France	Falcons	21
6	Adela	Voetberg	Falcons	26
7	Pasquale	Croston	Falcons	26
8	Colin	Hallett	Falcons	23
9	Nisha	Foard	Falcons	22
10	Rudy	Lagos	Falcons	24
11
12
13
14
15
16
17
Sheet2

The formula in cell B2 simply counts how many Falcons there are on the main page. This number is not used in the array formulas below it, it's for the user's information only. To build the matrix below, select from cell A5 down to as many members you ever expect any given team to have, plus a few. If you expect teams of twenty, I would select thirty rows so there is plenty of room for future expansion. Once you have the cells selected, enter this formula in the formula bar, and confirm it by hitting CONTROL + SHIFT + ENTER instead of the usual ENTER alone. This tells Excel to resolve the formula as an array:

=(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$C$2:$C$301=$B$1,ROW(Sheet1!$C$2:$C$301),99),ROW(INDIRECT("1:"&ROWS(Sheet1!$C$2:$C$301)))))&"")

This formula looks through the "Team" column on Sheet1 (Change that reference if your master page has a different sheet name), looking down through row 301 (farther than your current roster to capture future additions), and returns the Column A data (first names in your case) which correspond to "Falcons" entries only. They are returned in the same order they are listed in the Sheet1 worksheet. You may then use the fill handle at the bottom right corner of the array to drag the formulas to the right to populate columns B, C, and D.
Someday you may want to add more columns to the master sheet, maybe column E will be position, column F runs, column G hits, column H errors, etc.... You can drag the formulas to the right as many columns as you want, and the references will update.

You might notice in the snapshot of my spreadsheet that the formula in column C doesn't exactly match the others. Instead of:
=(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$C$2:$C$301=$B$1,ROW(Sheet1!$C$2:$C$301),99),ROW(INDIRECT("1:"&ROWS(Sheet1!$C$2:$C$301)))))&"")

you find this:
=(INDEX(Sheet1!C:C,SMALL(IF(Team=$B$1,ROW(Team),99),ROW(INDIRECT("1:"&ROWS(Team)))))&"")

I've created a named range on Sheet1 that expands and contracts to the exact number of rows for which there is team data. That named range means I will not have to change the array formulas on the team pages if my master roster grows beyond row 301. On Sheet1, select cell C1. Click on Insert, then Name, then Define... The name will default to the value in the selected cell, so it should already be called "Team". Enter the following formula in the "Refers to" block at the bottom of the dialog box, replacing what Excel put there:
=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C)-1,1)

edit: I see that when I built the formulas, Excel inserted the Sheet2! reference to the same-sheet cell references. In the explanatory text I have removed that, so you can use the formulas as presented in the text on a sheet of any name.

 

[psu1999]

Thank you so much for the quick response. That did the job perfectly. I will read it over and make sure I understand everything that the formula does..very thorough!

Bob

 

[Peter_SSs]

Bob

You might also have a look at my suggestion in post #3 of this thread. It does avoid the need for array formulas, but does use a 'helper' column on the original list sheet. (This helper column could be hidden once you have populated the formulas in it)