Selecting multiple rows based on a condition

[ppp]

Looking for a simple solution (hopefully) for the following:

I have a data sheet(see below) which has rows of players consisting of several columns of info on each player.

one of the columns indicates which team# each player belongs to.

In a separate team sheet(see below) I would like to specify the team# in the first column in the row followed by each team member in adjacent columns.

Data Sheet:

status team# jersey# first_name last_name stat1 stat2 stat3...
OK 1 4 test1_f test1_l x x x
OK 1 5 test2_f test2_l x x x
OK 1 6 test3_f test3_l x x x
OK 2 6 test4_f test4_l x x x
OK 2 7 test5_f test5_l x x x
OK 1 8 test6_f test6_l x x x
OK 3 4 test7_f test7_l x x x
OK 2 4 test8_f test8_l x x x
OK 3 6 test9_f test9_l x x x


Team Sheet: (to look like)

Team# Player#1 Player#2 Player#3 Player#4
1 test1_f test1_l test2_f test2_l test31_f test3_l test6_f test6_l
2 test4_f test4_l test5_f test5_l test81_f test8_l
3 test7_f test7_l test9_f test9_l


Any help would be greatly appreciated.

 

[Harvey]

you forgot to say what you want to do.

 

[ppp]

What I am trying to do is produce the team sheet from the data sheet, as per the layouts shown.

I tried nested if's but was not getting the results I had expected.

For example: I would like to select all rows from the data sheet where team#=1 and place the player names in a single row as per the layout of the team sheet.
Of course I would like this done for all team #'s

Hope this helps, Thanks again.

 

[Harvey]

insert a new column in sheet1, with this formula:
=B2&"_"&C2

like shown in the example

Map1
	A	B	C	D
1		team	player	name
2	1_1	1	1	a
3	1_2	1	2	b
4	1_3	1	3	c
5	2_1	2	1	d
6	2_2	2	2	e
7	2_3	2	3	f
8	1_4	1	4	g
Blad1

in sheet 2 put this formulas:

in A3:
=VLOOKUP(ROW(A1),Sheet1!B:B,1,FALSE)
and drag down.

in B2-B12:
the numbers 1-11

in B3:
=VLOOKUP($A3&"_"&B$2,Sheet1!$A$2:$D$15, 4, FALSE)
drag right until B12. then drag down.

you will get something like this:

Map1
	A	B	C	D	E	F
1	team	player
2		1	2	3	4	5
3	1	a	b	c	g	n
4	2	d	e	f	j	l
5	3	h	i	k	#N/B	#N/B
Blad2

You might want to put something in the formula to delete the #N/B too.

 

[ppp]

Thanks Harvey...very professional.

Solution worked like a charm.

Thank god for forums such as these that help us along in our daily persuits.

Just as an aside and I know this may sound like splitting hairs but would there be a similar solution without keeping track of the number of players (player) in sheet 1.

A solution that would just use team # and iterate through the rows returned with the same team #?

Thanks for the help Harvey it was greatly appreciated.

 

[Harvey]

the problem is that you cannot let a formula return a matrix, so you can never know the # of the team row in a formula. I've been puzzling through this for a while, but I did not find a solution. Anyway, you can hide the first column in sheet 1 so that your sheet will not look too ugly because of it

 

[ppp]

I think I understand the issues...was just trying to avoid needing to know what the next player number is in sheet 1 when adding a player to a team.

I guess I could sort sheet 1 by team#/player# so I know what the next player number is for each team.

Is it possible to automagically increment and populate the player# column once I enter the team# when entering a new player?

Of course if this is possible it would be nice if this solution filled in any holes in the player# sequence for a team due to removal of a player from a team.

Would a macro be required for this?