Listing All Who Match

A

agutts6

New Member
Joined
Jun 19, 2011
Messages
19
So I'm trying to create depth charts on the following criteria....

<table border="0" cellpadding="0" cellspacing="0" width="640"><col style="width:48pt" span="10" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">stat_id</td> <td style="width:48pt" width="64">
</td> <td class="xl65" style="width:48pt" width="64">Age</td> <td class="xl65" colspan="2" style="mso-ignore:colspan;width:96pt" width="128">Current Org</td> <td class="xl65" style="width:48pt" width="64">Primary</td> <td class="xl65" style="width:48pt" width="64">
</td> <td class="xl65" style="width:48pt" width="64">Number</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Mitch Abeita</td> <td>abeita001mit</td> <td align="right">542863</td> <td class="xl65">
</td> <td class="xl65" align="right">26.21</td> <td class="xl65">NYY</td> <td class="xl65">
</td> <td class="xl65">C</td> <td class="xl65">
</td> <td class="xl66" align="right">0.200</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Adam Abraham</td> <td>abraha001ada</td> <td align="right">488651</td> <td class="xl65">
</td> <td class="xl65" align="right">25.29</td> <td class="xl65">CLE</td> <td class="xl65">
</td> <td class="xl65">3B</td> <td class="xl65">
</td> <td class="xl66" align="right">0.220</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Matt Abraham</td> <td>abraha001mat</td> <td align="right">595105</td> <td class="xl65">
</td> <td class="xl65" align="right">25.46</td> <td class="xl65">TOR</td> <td class="xl65">
</td> <td class="xl65">??</td> <td class="xl65">
</td> <td class="xl66" align="right">0.125</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Abner Abreu</td> <td>abreu-001abn</td> <td align="right">506687</td> <td class="xl65">
</td> <td class="xl65" align="right">22.71</td> <td class="xl65">CHI</td> <td class="xl65">
</td> <td class="xl65">RF</td> <td class="xl65">
</td> <td class="xl66" align="right">0.209</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Jesus Abreu</td> <td>abreu-001jes</td> <td align="right">593644</td> <td class="xl65">
</td> <td class="xl65" align="right">20.21</td> <td class="xl65">ARI</td> <td class="xl65">
</td> <td class="xl65">2B</td> <td class="xl65">
</td> <td class="xl66" align="right">0.194</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Bobby Abreu</td> <td>abreu-001bob</td> <td align="right">110029</td> <td class="xl65">
</td> <td class="xl65" align="right">38.29</td> <td class="xl65">LAA</td> <td class="xl65">
</td> <td class="xl65">LF</td> <td class="xl65">
</td> <td class="xl66" align="right">0.275</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Jean Acevedo</td> <td>aceved001mic</td> <td align="right">541638</td> <td class="xl65">
</td> <td class="xl65" align="right">21.54</td> <td class="xl65">SEA</td> <td class="xl65">
</td> <td class="xl65">3B</td> <td class="xl65">
</td> <td class="xl66" align="right">0.174</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Dustin Ackley</td> <td>ackley001dus</td> <td align="right">554429</td> <td class="xl65">
</td> <td class="xl65" align="right">24.38</td> <td class="xl65">SEA</td> <td class="xl65">
</td> <td class="xl65">2B</td> <td class="xl65">
</td> <td class="xl66" align="right">0.276</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Mayobanex Acosta</td> <td>acosta001may</td> <td align="right">521309</td> <td class="xl65">
</td> <td class="xl65" align="right">24.63</td> <td class="xl65">TB</td> <td class="xl65">
</td> <td class="xl65">C</td> <td class="xl65">
</td> <td class="xl66" align="right">0.192</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Pedro Acosta</td> <td>acosta001ped</td> <td align="right">576848</td> <td class="xl65">
</td> <td class="xl65" align="right">21.96</td> <td class="xl65">FLA</td> <td class="xl65">
</td> <td class="xl65">C</td> <td class="xl65">
</td> <td class="xl66" align="right">0.163</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Cristhian Adames</td> <td>adames001cri</td> <td align="right">542436</td> <td class="xl65">
</td> <td class="xl65" align="right">20.96</td> <td class="xl65">COL</td> <td class="xl65">
</td> <td class="xl65">SS</td> <td class="xl65">
</td> <td class="xl66" align="right">0.180</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Joshua Adams</td> <td align="center">#N/A</td> <td align="right">518392</td> <td class="xl65">
</td> <td class="xl65" align="right">23.29</td> <td class="xl65">FLA</td> <td class="xl65">
</td> <td class="xl65">3B</td> <td class="xl65">
</td> <td class="xl66" align="right">0.171</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Lane Adams</td> <td>adams-001lan</td> <td align="right">572669</td> <td class="xl65">
</td> <td class="xl65" align="right">22.63</td> <td class="xl65">KC</td> <td class="xl65">
</td> <td class="xl65">CF</td> <td class="xl65">
</td> <td class="xl66" align="right">0.183</td> </tr> </tbody></table>
(Here's hoping that displays)

Now, on a different sheet, I'll have a setup like the following...

<table border="0" cellpadding="0" cellspacing="0" width="1048"><col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="2" width="40"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="2" width="40"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="2" width="40"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="2" width="40"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="2" width="40"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="2" width="40"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="2" width="40"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:46pt" height="20" width="61">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:46pt" width="61">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:46pt" width="61">CIN</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:46pt" width="61">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:46pt" width="61">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:46pt" width="61">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:46pt" width="61">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">
</td> <td class="xl65" style="width:46pt" width="61">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1B</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">2B</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">3B</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">SS</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">LF</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">CF</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">RF</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">C</td> </tr> </tbody></table>

Where I'll have a team name (above, thats CIN) and a number of positions.

What I'd like to have happen is all CIN players who play 1B to populate under the 1B heading. Ideally, they'd also sort according to 'number' (the decimal on my first data sheet).

I really don't know where to start here. Doesn't seem like some iteration of index:match will cut it...
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
So this may be a little round about, but how about this:

Assumptions for "Players' sheet:
Col A - New
Col B - Number
Col C - Name
Col D - Key?
Col D - Stat_Id
Col F - Age
Col G - Current Org
Col H - Position

* Sort your players by 1. Current Org, 2. Position, 3. Number
* Create a "counter" in column A:
- A2 = IF(F2&G2=F1&G1,A1+1,1)
- Copy a2 down beside all players

* Create Name Ranges (Can be >1,000 if necessary, but all the same)
Counter = Players!$A$2:$A$1000
Data_Lookup = Players!$B$2:$C$1000
Number = Players!$B$2:$B$1000
Player = Players!$C$2:$C$1000
Position = Players!$H$2:$H$1000
Team = Players!$G$2:$G$1000

Assumption for depth chart:

Col A - New / Unique key created on player sheet
Row 2 - Positions (B = 1B, C = 2B, etc)
Cell F1 - Team Name (Must match the abbreviation on player sheet)

* Put in the counter
Cell A3 = 1, Cell A4 = 2, Cell A5 = 3 etc (However deep you want to go)

Use formula to find the matching team, position, and depth chart position. Cell B3= IF(ISERROR(VLOOKUP(SUMPRODUCT((Team=$F$1)*(Position=B$2)*(Counter=$A3)*(Number)),Data_Lookup,2,0)),"",VLOOKUP(SUMPRODUCT((Team=$F$1)*(Position=B$2)*(Counter=$A3)*(Number)),Data_Lookup,2,0))

Formula may look like a beast, but it isn't that bad:
1. The sumproduct is used to find the player number
2. Vlookup uses the player number to return a name
3. The "Iserror" avoids a visible "NA" and just brings a blank space.

So anyhow, I got it to work though it seems monsterous. If you want the spreadsheet as an example, e-mail me at jsayre@mtrgaming.com and I will send it to you.

Jeff
 
Upvote 0
Not sorted by number but hope it helps:
Excel Workbook
ABCDEFGHIJ
1stat_idAgeCurrent OrgPrimaryNumber
2Mitch Abeitaabeita001mit54286326.21NYYC0.2
3Adam Abrahamabraha001ada48865125.29CLE3B0.22
4Matt Abrahamabraha001mat59510525.46TOR??0.125
5Abner Abreuabreu-001abn50668722.71CHIRF0.209
6Jesus Abreuabreu-001jes59364420.21ARI2B0.194
7Bobby Abreuabreu-001bob11002938.29LAALF0.275
8Jean Acevedoaceved001mic54163821.54SEA3B0.174
9Dustin Ackleyackley001dus55442924.38SEA2B0.276
10Mayobanex Acostaacosta001may52130924.63TBC0.192
11Pedro Acostaacosta001ped57684821.96FLAC0.163
12Cristhian Adamesadames001cri54243620.96COLSS0.18
13Joshua Adams#N/A51839223.29FLA3B0.171
14Lane Adamsadams-001lan57266922.63KCCF0.183
15
16
171B2B3BSSLFCFRFC
18 0.1940.220.18   0.2
19 0.2760.174    0.192
20  0.171    0.163
Sheet4
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
VBA for Web Scraping help
Replies
0
Views
618
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
S
  • Question
How to click a link inside a table using VBA
Replies
0
Views
2K
swapnilk
S
B
  • Question
Webscrapping Loop to get table data
Replies
1
Views
561
John_w
J

Forum statistics

Threads
1,215,873
Messages
6,127,454
Members
449,383
Latest member
DonnaRisso

Share this page

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
Back
Top