Help with matching data (MIN, MAX) between sheets

[Armadillo1]

Hey guys,

So here is my issue. I'm trying to sort through a plethora of historical sports stats and data, in order to create a single season ecord page for each team. I have all of the data sorted in individual columns for every player's stats. So for example, I have every player who played for Team A in Season 1, and his corresponding stats...so Column A is the player name, Column B the team, Column C his goals, Column D his assists, Column E his points, and so on and so forth.

Now, what I want to do on another sheet, is write a formula that gives me single season highs for goals, assists, points etc etc etc, for each team. I'm thinking this has to do with a type of VLOOKUP/MAX (and MIN, when applicable) hybrid formula, but I just don't know how to code it.

Any help would be massively, massively appreciated.

Thanks in advance!

[vlady]

=MAX(IF(Sheet2!A1:A100=A1,Sheet2!B1:B100))

sheet2 A1:A100 the lists of the players in sheet2

A1 what team name to find the max(new sheet)

Sheet2 B1:B100 is say the range of all goals..



if this doesn't help maybe a sample of the worbook your working could help us.

thanks.
can you post a sample workbook your working with..

[jeffreybrown]

This might give you an idea

[Armadillo1]

Thanks for the advice guys. With regards to posting it, is there a way I can post the entire thing? Sorry for such a newbie question.

[jeffreybrown]

When you say entire thing, sure, you can post your entire workbook, but before you upload check the size limits.

If you bust the size limits you may try zipping.

[Armadillo1]

Ok guys, thanks for the help, here is my abridged version of what we're looking at.


The "Final Product" sheet will show the records (assuming this works). Don't worry about the slots in blue that say things like G.W (Goalie wins) G.L. (Goalie loses) etc, as I haven't updated that yet.

Anyway, thanks a lot for the advice, it is greatly appreciated!

[MarvinP]

Hi Armadillo1 and welcome to the forum,

Pivot Tables can do wonders for understanding tables of data. See attached where I have an example of the top 5 players goals by season.

I have no idea what more you need but this is an example of what Pivots can do.

[Armadillo1]

Hey, thanks for the reply. Basically what I'm looking to do is to find a team's single-season record. So for all of the players who have "Boston" next to their name, representing different seasons, I am looking for the one guy who scored the most goals, in any one season (and most assists, points, etc etc). This is to then be showed on Sheet 3 (Final Product).

I'll check out the pivots though, thanks.

[MarvinP]

Hi,

On the Stats page I did a simple Pivot to show what you asked for. Learn Pivots and it can do what you want very easily.

[vlady]

Hello Armadillo1

the reply of Marvin is the best solution for your query...

but here's a sample formula that i think gives the correct answer with some explanation which i think eventually will lead you to using Pivots....


Career Stats Abridged.xlsx

hope some of the formula's here will also contribute in your worksheet.