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

#### myacovone

##### New Member
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

Last edited:

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Note that the formula in E2 is an array formula (confirm with ctrl+shift+enter). Copy all formulas down.
Excel Workbook
ABCDEF
1NameScoreNameBestAttempts
2Tom0Tom1005
3Tom50Rick801
4Tom75Harry1002
5Tom100
6Tom90
7Rick80
8Harry100
9Harry95
Sheet1

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>

 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

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.

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.

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?

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?

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.

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.

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.

Replies
1
Views
677
Replies
1
Views
260
Replies
1
Views
364
Replies
14
Views
585
Replies
5
Views
2K

1,203,461
Messages
6,055,559
Members
444,798
Latest member
PAO1609

### 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.

### Which adblocker are you using?

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

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