Help with indexing data for multiple value returns

L

leehowardblair

New Member
Joined
Jul 18, 2011
Messages
9
So I have a bunch of sports stats data... I need to return the players name only 1 time, but with multiple "stat" categories for said player. So I have a macro that allows me to easily get a top 10 of "unique" player names. But then I need a few stats from that player. The problem is that the data comes in on separate rows and some players come in a multiple positions, but I only need the data once.

So here's the data feed:

Excel 2012
CDEFGHIJKLM
1FULL_NAMEfirstNamelastNamepositionscoresalaryteamStatIdNameAbbreviationFantasyPoints
2Alex LenAlexLenC9.305100Pho22ReboundsREB3.75
3Alex LenAlexLenC9.305100Pho253 Points Made3PM0
4Alex LenAlexLenC9.305100Pho27AssistsAST0
5Alex LenAlexLenC9.305100Pho28BlocksBLK0
6Alex LenAlexLenC9.305100Pho29StealsSTL0
7Alex LenAlexLenC9.305100Pho31TurnoversTO-0.5
8Alex LenAlexLenC9.305100Pho32PointsPTS6
9Alex LenAlexLenC9.305100Pho212Double DoubleDDbl0
10Alex LenAlexLenC9.305100Pho213Triple DoubleTDbl0
11Alex LenAlexLenUTIL9.305100Pho22ReboundsREB3.75
12Alex LenAlexLenUTIL9.305100Pho253 Points Made3PM0
13Alex LenAlexLenUTIL9.305100Pho27AssistsAST0
14Alex LenAlexLenUTIL9.305100Pho28BlocksBLK0
15Alex LenAlexLenUTIL9.305100Pho29StealsSTL0
16Alex LenAlexLenUTIL9.305100Pho31TurnoversTO-0.5
17Alex LenAlexLenUTIL9.305100Pho32PointsPTS6
18Alex LenAlexLenUTIL9.305100Pho212Double DoubleDDbl0
19Alex LenAlexLenUTIL9.305100Pho213Triple DoubleTDbl0
20Emmanuel MudiayEmmanuelMudiayPG8.305900Den22ReboundsREB1.25
21Emmanuel MudiayEmmanuelMudiayPG8.305900Den253 Points Made3PM0
22Emmanuel MudiayEmmanuelMudiayPG8.305900Den27AssistsAST4.5
23Emmanuel MudiayEmmanuelMudiayPG8.305900Den28BlocksBLK0
24Emmanuel MudiayEmmanuelMudiayPG8.305900Den29StealsSTL0
25Emmanuel MudiayEmmanuelMudiayPG8.305900Den31TurnoversTO-1.5
26Emmanuel MudiayEmmanuelMudiayPG8.305900Den32PointsPTS4
27Emmanuel MudiayEmmanuelMudiayPG8.305900Den212Double DoubleDDbl0
28Emmanuel MudiayEmmanuelMudiayPG8.305900Den213Triple DoubleTDbl0
29Emmanuel MudiayEmmanuelMudiayG8.305900Den22ReboundsREB1.25
30Emmanuel MudiayEmmanuelMudiayG8.305900Den253 Points Made3PM0
31Emmanuel MudiayEmmanuelMudiayG8.305900Den27AssistsAST4.5
32Emmanuel MudiayEmmanuelMudiayG8.305900Den28BlocksBLK0
33Emmanuel MudiayEmmanuelMudiayG8.305900Den29StealsSTL0
34Emmanuel MudiayEmmanuelMudiayG8.305900Den31TurnoversTO-1.5
35Emmanuel MudiayEmmanuelMudiayG8.305900Den32PointsPTS4
36Emmanuel MudiayEmmanuelMudiayG8.305900Den212Double DoubleDDbl0
37Emmanuel MudiayEmmanuelMudiayG8.305900Den213Triple DoubleTDbl0
38Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den22ReboundsREB1.25
39Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den253 Points Made3PM0
40Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den27AssistsAST4.5
41Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den28BlocksBLK0
42Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den29StealsSTL0
43Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den31TurnoversTO-1.5
44Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den32PointsPTS4
45Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den212Double DoubleDDbl0
46Emmanuel MudiayEmmanuelMudiayUTIL8.305900Den213Triple DoubleTDbl0
NBA_DATA
Cell Formulas
RangeFormula
C2=[@firstName]&" "&[@lastName]

So I need to return the player only once, regardless of his position and then a few stats (in order score leader), like
Excel 2012
QRSTUVW
FULL_NAMEPOSITIONTEAMSCOREPTS3PMASSISTS
Alex LenCPhoHELP???HELP???HELP???
Emmanuel MudiayPGDenHELP???HELP???HELP???
C.J. McCollumSGPorHELP???HELP???HELP???
Noah VonlehPFPorHELP???HELP???HELP???
Leandro BarbosaPGPhoHELP???HELP???HELP???
Lou WilliamsSGLALHELP???HELP???HELP???
Allen CrabbeSGPorHELP???HELP???HELP???
Julius RandlePFLALHELP???HELP???HELP???
Tyler UlisPGPhoHELP???HELP???HELP???
Dragan BenderPFPhoHELP???HELP???HELP???

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]9.3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8.3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6.8[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6.5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5.3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4.8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3.5[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3.5[/TD]

</tbody>
NBA_DATA​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]=listUnique($C:$C)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R2[/TH]
[TD="align: left"]=VLOOKUP($Q2,$C:$O,4,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]S2[/TH]
[TD="align: left"]=VLOOKUP($Q2,$C:$O,7,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]T2[/TH]
[TD="align: left"]=VLOOKUP($Q2,$C:$O,5,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Thanks very much in advance - using excel to try to get what I need out of this xml feed.
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
You can use sumifs or a pivot table.


Excel 2010
MNOPQRS
1FULL_NAMEPOSITIONTEAMSCOREPTS3PMASSISTS
2Alex LenCPho9.31200
3Emmanuel MudiayPGDen8.312013.5
4C.J. McCollumSGPor6.8000
5Noah VonlehPFPor6.5000
Sheet1
Cell Formulas
RangeFormula
Q2=SUMIFS($L$2:$L$46,$B$2:$B$46,M2,$J$2:$J$46,"Points")
R2=SUMIFS($L$2:$L$46,$B$2:$B$46,M2,$J$2:$J$46,"3 Points Made")
S2=SUMIFS($L$2:$L$46,$B$2:$B$46,M2,$J$2:$J$46,"Assists")
 
Upvote 0
LloydFinancials said:
You can use sumifs or a pivot table.

Excel 2010
MNOPQRS
FULL_NAMEPOSITIONTEAMSCOREPTS3PMASSISTS
Alex LenCPho
Emmanuel MudiayPGDen
C.J. McCollumSGPor
Noah VonlehPFPor

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]9.3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8.3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13.5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]=SUMIFS($L$2:$L$46,$B$2:$B$46,M2,$J$2:$J$46,"Points")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]R2[/TH]
[TD="align: left"]=SUMIFS($L$2:$L$46,$B$2:$B$46,M2,$J$2:$J$46,"3 Points Made")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]S2[/TH]
[TD="align: left"]=SUMIFS($L$2:$L$46,$B$2:$B$46,M2,$J$2:$J$46,"Assists")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

This is working great and makes perfect sense... However, the feed shows a player at all possible positions and therefore the data is sometimes doubled or tripled. For example, Alex Len is listed as a C as well as UTIL, so it returns 12 points for him, in reality he only has 6. Is there a way to add an if clause maybe for this? Some players have 3 positons too.

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Indexing/Match Question
Replies
1
Views
534
AlanY
A
P
  • Question Question
Replace every occurrence of "ABC" with "123", "DEF" with "456 within a column
Replies
3
Views
793
jproffer
J
D
  • Question Question
Help Please. How to keep a running total from multible events.
Replies
6
Views
911
Peter_SSs
Peter_SSs
G
  • Question Question
Data validation drop down list source to lookup a value and list unused items ?
Replies
0
Views
1K
gaftalik
G
T
  • Question Question
Look up and return value based on more than one factor
Replies
4
Views
890
toMarsandBack
T

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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