Extract data from TABLE, based on DATE and find MAX score, #of players, and player's name.

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Hello,
First let me give you a little background on my problem. This is my first post in this forum. I have tried to find a solution to my problem by searching the web and several forums. However, I have not found one. I have been able to get some segments to work but need some assistance to finish my creation.

I have three table top Skeeball machines will accommodate two players on each game. The machines keep score for each round of two players, and each Christmas my family has a friendly competition playing Skeeball

I have created several sheets in Excel to generate the tournament brackets, track players, determine winners of each game, and announce the winner of the competition. (With macros to control each function, for adding players, moving data, and printing winner certificates. I want to add a sheet that will store the scores, players, and champion, of each year.

I have a workbook with the following configuration:

Worksheet NameContents
SetupSetup information
Team names / Player names
Seed information
8 Teams / Players
16 Teams / Players
Tournament Bracket
8 Team Score
16 Team Score
Scores by Team / Player for each round of play
First Round
Second Round
Quarter Final Round
Final Round
PlayersTeam / Player Names
Annual ScoresRecord of all games played by teams or players
High ScoresRecord of highest scores
ChampionsRecord of the champions for each year

<tbody>
</tbody>

You can see below that; columns A through H contains sample (totally fictitious) scores recorded by year.
ABCDEFGHIJKLMNOP
1SCORES RECORDED BY YEARDateMax Score# Players Max scorePlayerPlayerPlayerPlayer
2DatePlayerGame 1Game 2Game 3Game 4Game 5Tie
325 Dec 16Player 131602303604302406025 Dec 16
425 Dec 16Player 81702203704202306025 Jan 17
525 Jan 17Player 5180210380410220160
625 Jan 17Player 10190200390400210380
725 Dec 18Player 3242601602140260
825 Dec 18Player 21410170115010

<tbody>
</tbody>

What I want to do:
Based on the date, in column J, I want to extract the highest score (column K) and the player (column L). I would also like to know, if there are multiple players with the highest score. Perhaps placing additional players in columns M, N, O, and P.

In other words, I want to specify the data ranges as follows:
Date: $A$3:$A$5000
Scores: $C$3:$H$5000

At the end of our tournament, the only input from me, would be to enter the date 25 Dec 18 into cell J5, and the formula in K5 would tell me the max score for all games played on 25 Dec 2018. The formula in K5 will display the number of players that have that max score, and if there were more than one player to have the same max score, the player name would be displayed in N5, O5, and P5.

Formulas used:
K5 – =Max(if(J5=$A$3:$A$5000,Scores) – Doesn’t work returns max value in scores.
K5 – =INDEX(MAX($$A$3:$A$5000),MATCH(J3,$A$3:$A$5000)) =Doesn’t work returns #REF!
L5 – =COUNTIF(Date,J5) – This only counts the number of times that the date in column A matches the date in column J

Can someone please offer me some direction? I can’t find the ‘key’ to unlock this mystery.

Thanks,

Rod
Thanks,

Rod
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi & welcome to the board.
Did you confirm the Max(If formula with Ctrl Shift Enter, rather than just enter? If so it should be wrapped in {}
For L5 try
=SUMPRODUCT(($A$3:$A$8=$J3)*($C$3:$H$8=$K3))
 
Upvote 0
All three formula



Excel 2013/2016
ABCDEFGHIJKLMNOP
1SCORES RECORDED BY YEARDateMax Score# Players Max scorePlayerPlayerPlayerPlayer
2DatePlayerGame 1Game 2Game 3Game 4Game 5Tie
325-Dec-16Player 131602303604302406025-Dec-164303Player 13Player 8Player 5 
425-Dec-16Player 817022037042043060
525-Dec-16Player 5180430380410220160
625-Jan-17Player 10190200390400210380
725-Dec-18Player 3242601602140260
825-Dec-18Player 21410170115010
PriorDay
Cell Formulas
RangeFormula
L3=SUMPRODUCT(($A$3:$A$8=$J3)*($C$3:$H$8=$K3))
K3{=MAX(IF($A$3:$A$8=$J3,$C$3:$H$8))}
M3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:A))),"")}
N3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:B))),"")}
O3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:C))),"")}
P3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:D))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Fluff,

I apologize for replying so late. I have some health issues and don’t always have the ability to do the things I would like to accomplish.

However, I want to thank you for answering my questions. Your solutions were spot on!! They work perfectly. I don’t know how much time it took you to author the formulas, but I had spent more than 20 hours researching and trying to author solutions.

Thank you again!

Nitehawkhp
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Fluff,

I have experienced a small problem with the formulas when I tried to extract the high scores for the following year, for example:

The columns J, K, L, M, N, O, P is where the issue is.

JKLMNOP
1DateMax Score# Players Max ScorePlayerPlayerPlayerPlayer
225-Dec-20165003Player 8Player 13Player 16
325-Dec-20174701Player 3Player 8Player 14
425-Dec-20183601Player 2Player 5Player 10Player 13

<tbody>
</tbody>


CellFormula
K3{=MAX(IF($A$3:$A$8=$J3,$C$3:$H$8))}
M3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:A))),"")}
N3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:B))),"")}
O3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:C))),"")}
P3{=IFERROR(INDEX($B$3:$B$8,SMALL(IF($C$3:$H$8=$K3,ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:D))),"")}

<thead>
</thead><tbody>
</tbody>

The formulas in row 2 work perfectly. The're no issue with those formulas.

The issues result when the formulas are copied to all rows below row 2.
The database that contains the dates, scores, and players contain the following information:

Data Player Game 1 Game 2 Game 3 Game 4 Game 5 Tie
25-Dec-2016 - Player 3 - 120 - 480 - 420 - 410 - 470 - 60
25-Dec-2016 - Player 8 - 470 - 380 - 490 - 500 - 370 - 60
25-Dec-2017 - Player 14 - 90 - 360 - 470 - 440 - 350 - 80

When the formulas in K3 and L3, extract the data from the database, they correctly identify (470) as Max score and one player (Player 14) for 25-Dec-2017.
When the formulas in M3, N3, and O3 extract the data from the database for 25-Dec-2017, they are finding the players (Player 3) and (Player 8) with 470 on 25-Dec-2016.

Thank you for any help you can offer to me.
 
Upvote 0
How about
=IFERROR(INDEX($B$3:$B$8,SMALL(IF(($C$3:$H$8=$K3)*($A$3:$A$8=$J3),ROW($A$3:$A$8)-ROW($A$3)+1),COLUMNS($A:A))),"")
 
Upvote 0
Fluff,

That fixed it. Works just like I needed it to. I'm very appreciative for your help.
Do you have any recommendation on where I can learn more about array, large, and small formulas?

Thank you!

Nitehawkhp
 
Upvote 0
You're welcome & thanks for the feedback.

ExcelJet has quite a lot of useful formulae
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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