Need Help Removing Rows, Keeping Highest Score, and Counting Records (Optional)

[myacovone]

Here's what I need to do:

This is the raw data I have to work with:

Name Score

Tom	0
Tom	50
Tom	75
Tom	100
Tom	90
Rick	80
Harry	100
Harry	95
And... Here's how I want it to look
Name	Highest Score
Tom	100
Rick	80
Harry	100
If that's all I can figure out, that would be great. It would be even better if I could produce this:
Name	Highest Score	Attempts
Tom	100	5
Rick	80	1
Harry	100	2

<tbody>
</tbody>

Looking forward to your responses. Thanks!!

Mark

 

[JoeMo]

Note that the formula in E2 is an array formula (confirm with ctrl+shift+enter). Copy all formulas down.

Excel Workbook
	A	B	C	D	E	F
1	Name	Score		Name	Best	Attempts
2	Tom	0		Tom	100	5
3	Tom	50		Rick	80	1
4	Tom	75		Harry	100	2
5	Tom	100
6	Tom	90
7	Rick	80
8	Harry	100
9	Harry	95
Sheet1

 

[myacovone]

Joe, this is exactly what I need. However, I'm new to Array Formulas and not exactly sure what to do (also I'm on a Mac). Any advice would be appreciated. Thank you!

Mark

Note that the formula in E2 is an array formula (confirm with ctrl+shift+enter). Copy all formulas down.
Sheet1

*	A	B	C	D	E	F
1	Name	Score	*	Name	Best	Attempts
2	Tom	0	*	Tom	100	5
3	Tom	50	*	Rick	80	1
4	Tom	75	*	Harry	100	2
5	Tom	100	*	*	*	*
6	Tom	90	*	*	*	*
7	Rick	80	*	*	*	*
8	Harry	100	*	*	*	*
9	Harry	95	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 {=MAX(IF($A$2:$A$9=D2,$B$2:$B$9))} F2 =COUNTIF($A$2:$A$9,D2) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JoeMo]

Joe, this is exactly what I need. However, I'm new to Array Formulas and not exactly sure what to do (also I'm on a Mac). Any advice would be appreciated. Thank you!

Mark

I don't use a MAC but i think this will do it.
In whatever cell you want to enter the formula that's in E2 in my example, enter:

=MAX(IF($A$2:$A$9=D2,$B$2:$B$9))
(extend or compress the ranges as needed to cover all the raw data)

then instead of just pressing the "Enter" key as you would do for a regular formula, hold down the Command key and press enter. If you look in the formula bar you should now see curly braces - { and } surrounding the entire formula.
You can now copy or drag the formula down to cover your name list just like a regular formula.

 

[myacovone]

Thanks Joe... I'm getting closer... I definitely have the formula entered correctly but when I drag it down I'm just getting zeros in the columns. May try to do this on a PC to see if my results are any different. Thanks again.

I don't use a MAC but i think this will do it.
In whatever cell you want to enter the formula that's in E2 in my example, enter:

=MAX(IF($A$2:$A$9=D2,$B$2:$B$9))
(extend or compress the ranges as needed to cover all the raw data)

then instead of just pressing the "Enter" key as you would do for a regular formula, hold down the Command key and press enter. If you look in the formula bar you should now see curly braces - { and } surrounding the entire formula.
You can now copy or drag the formula down to cover your name list just like a regular formula.

 

[JoeMo]

Thanks Joe... I'm getting closer... I definitely have the formula entered correctly but when I drag it down I'm just getting zeros in the columns. May try to do this on a PC to see if my results are any different. Thanks again.

Are you using absolute references for the ranges occupied by your raw data (name and score)? Is Calculation set to Automatic?

 

[myacovone]

I'm using the formulas exactly as you kindly provided above. And I just checked Calculation and it's set to Automatic. Is there a way to upload the sample file here?

 

[JoeMo]

I'm using the formulas exactly as you kindly provided above. And I just checked Calculation and it's set to Automatic. Is there a way to upload the sample file here?

Check your Mr. Excel Inbox - I will give you an email address where you can send your file.

 

[myacovone]

Thank you Joe! That worked and you are amazing. I appreciate the assistance... saved me a ton of time!

Thanks!

Mark

Check your Mr. Excel Inbox - I will give you an email address where you can send your file.

 

[JoeMo]

Thank you Joe! That worked and you are amazing. I appreciate the assistance... saved me a ton of time!

Thanks!

Mark

You are welcome - thanks for the reply.