Selecting multiple rows based on a condition

ppp

New Member
Joined
Dec 8, 2004
Messages
9
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ppp

New Member
Joined
Dec 8, 2004
Messages
9
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

Well-known Member
Joined
Nov 18, 2004
Messages
953
insert a new column in sheet1, with this formula:
=B2&"_"&C2

like shown in the example
Map1
ABCD
1teamplayername
21_111a
31_212b
41_313c
52_121d
62_222e
72_323f
81_414g
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
ABCDEF
1teamplayer
212345
31abcgn
42defjl
53hik#N/B#N/B
Blad2


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

ppp

New Member
Joined
Dec 8, 2004
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Nov 18, 2004
Messages
953
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

New Member
Joined
Dec 8, 2004
Messages
9
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?
 

Forum statistics

Threads
1,148,526
Messages
5,747,211
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top