Help with indexing data for multiple value returns

[leehowardblair]

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
	C	D	E	F	G	H	I	J	K	L	M
1	FULL_NAME	firstName	lastName	position	score	salary	team	StatId	Name	Abbreviation	FantasyPoints
2	Alex Len	Alex	Len	C	9.30	5100	Pho	22	Rebounds	REB	3.75
3	Alex Len	Alex	Len	C	9.30	5100	Pho	25	3 Points Made	3PM	0
4	Alex Len	Alex	Len	C	9.30	5100	Pho	27	Assists	AST	0
5	Alex Len	Alex	Len	C	9.30	5100	Pho	28	Blocks	BLK	0
6	Alex Len	Alex	Len	C	9.30	5100	Pho	29	Steals	STL	0
7	Alex Len	Alex	Len	C	9.30	5100	Pho	31	Turnovers	TO	-0.5
8	Alex Len	Alex	Len	C	9.30	5100	Pho	32	Points	PTS	6
9	Alex Len	Alex	Len	C	9.30	5100	Pho	212	Double Double	DDbl	0
10	Alex Len	Alex	Len	C	9.30	5100	Pho	213	Triple Double	TDbl	0
11	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	22	Rebounds	REB	3.75
12	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	25	3 Points Made	3PM	0
13	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	27	Assists	AST	0
14	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	28	Blocks	BLK	0
15	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	29	Steals	STL	0
16	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	31	Turnovers	TO	-0.5
17	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	32	Points	PTS	6
18	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	212	Double Double	DDbl	0
19	Alex Len	Alex	Len	UTIL	9.30	5100	Pho	213	Triple Double	TDbl	0
20	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	22	Rebounds	REB	1.25
21	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	25	3 Points Made	3PM	0
22	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	27	Assists	AST	4.5
23	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	28	Blocks	BLK	0
24	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	29	Steals	STL	0
25	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	31	Turnovers	TO	-1.5
26	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	32	Points	PTS	4
27	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	212	Double Double	DDbl	0
28	Emmanuel Mudiay	Emmanuel	Mudiay	PG	8.30	5900	Den	213	Triple Double	TDbl	0
29	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	22	Rebounds	REB	1.25
30	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	25	3 Points Made	3PM	0
31	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	27	Assists	AST	4.5
32	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	28	Blocks	BLK	0
33	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	29	Steals	STL	0
34	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	31	Turnovers	TO	-1.5
35	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	32	Points	PTS	4
36	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	212	Double Double	DDbl	0
37	Emmanuel Mudiay	Emmanuel	Mudiay	G	8.30	5900	Den	213	Triple Double	TDbl	0
38	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	22	Rebounds	REB	1.25
39	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	25	3 Points Made	3PM	0
40	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	27	Assists	AST	4.5
41	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	28	Blocks	BLK	0
42	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	29	Steals	STL	0
43	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	31	Turnovers	TO	-1.5
44	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	32	Points	PTS	4
45	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	212	Double Double	DDbl	0
46	Emmanuel Mudiay	Emmanuel	Mudiay	UTIL	8.30	5900	Den	213	Triple Double	TDbl	0
NBA_DATA

Cell Formulas
Range	Formula
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

	Q	R	S	T	U	V	W
1	FULL_NAME	POSITION	TEAM	SCORE	PTS	3PM	ASSISTS
2	Alex Len	C	Pho	9.3	HELP???	HELP???	HELP???
3	Emmanuel Mudiay	PG	Den	8.3	HELP???	HELP???	HELP???
4	C.J. McCollum	SG	Por	6.8	HELP???	HELP???	HELP???
5	Noah Vonleh	PF	Por	6.5	HELP???	HELP???	HELP???
6	Leandro Barbosa	PG	Pho	5.3	HELP???	HELP???	HELP???
7	Lou Williams	SG	LAL	5	HELP???	HELP???	HELP???
8	Allen Crabbe	SG	Por	4.8	HELP???	HELP???	HELP???
9	Julius Randle	PF	LAL	4	HELP???	HELP???	HELP???
10	Tyler Ulis	PG	Pho	3.5	HELP???	HELP???	HELP???
11	Dragan Bender	PF	Pho	3.5	HELP???	HELP???	HELP???

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

NBA_DATA​

Worksheet Formulas Cell Formula Q2 =listUnique($C:$C) R2 =VLOOKUP($Q2,$C:$O,4,FALSE) S2 =VLOOKUP($Q2,$C:$O,7,FALSE) T2 =VLOOKUP($Q2,$C:$O,5,FALSE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

 

[LloydFinancials]

You can use sumifs or a pivot table.

Excel 2010
	M	N	O	P	Q	R	S
1	FULL_NAME	POSITION	TEAM	SCORE	PTS	3PM	ASSISTS
2	Alex Len	C	Pho	9.3	12	0	0
3	Emmanuel Mudiay	PG	Den	8.3	12	0	13.5
4	C.J. McCollum	SG	Por	6.8	0	0	0
5	Noah Vonleh	PF	Por	6.5	0	0	0
Sheet1

Cell Formulas
Range	Formula
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")

 

[leehowardblair]

You can use sumifs or a pivot table.

Excel 2010

	M	N	O	P	Q	R	S
1	FULL_NAME	POSITION	TEAM	SCORE	PTS	3PM	ASSISTS
2	Alex Len	C	Pho	9.3	12	0	0
3	Emmanuel Mudiay	PG	Den	8.3	12	0	13.5
4	C.J. McCollum	SG	Por	6.8	0	0	0
5	Noah Vonleh	PF	Por	6.5	0	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

 

[leehowardblair]

I think I fixed it. I added another criteria that must match the postion found on the VLOOKUP. Thanks for the help!