Need to find highest value in a column for each name in a separate column

Example.xlsbHi!

My name is Chris. I am new to the forums, and relatively new to Excel; I am still learning it. I can't get my head around this and was hoping for some help. (I am using Excel 2008 on an iMac with Mountain Lion)

This is a generalized example of what I want to do.

My excel sheet has a list of names (my sales executives) in column C. Column F has a single digit number in it. I'd like to find the highest number for each name in the list, and have that information appear on a separate worksheet. I've explained below and given examples to hopefully show you what i want to do. I also attached an example, cuz it looks funny when I preview the below info...


Example: (I hope this is clear)

C1 Header F1 Header
C2 John Doe F2 3
C3 John Doe F3 1
C4 Jane Dee F4 4
C5 Hal King F5 1
C6 Jack Frost F6 2
C7 John Doe F7 1
C8 Sally Queen F8 1
C9 Hal King F9 1
C10 Jane Dee F10 3



On the 2nd worksheet, I want to keep a list of each seller and their highest number.

Worksheet 2 example of what I am looking for:

A1 Name D1 Highest number

A2 John Doe D2 3
A3 Jane Dee D3 4
A4 Sally Queen D4 1
A5 Hal King D5 1
A6 Jack Frost D6 2


I use worksheet 2 to "report" the information from worksheet 1 for each seller. There is other data on worksheet two for each of my sellers that is calculated with formulas as well (I'll probably need help with those later on, lol) but to start, I wanted to ask the best way to get this to work? I've seen that array formulas can do things, but my sheets are over 100 rows deep and I don't want to have to do that for each cell.

I hope this makes sense...I am sure I am revealing my lack of knowledge in my question, and am open to your thoughts on how to best do this.

Thank you so much. I've seen a lot of great answers on this forum when looking through it, and they've helped me with other things. I look forward to your thoughts on this!

If you put your unique names in column A of sheet 2, starting at row 2 and then in B2 on that sheet put the formula:

=MAX(INDEX(Sheet1!$F$2:$F$25*(Sheet1!$C$2:$C$25=A2),0))

And copy down, that should do the job.

Thank you so much for the quick reply! This worked perfectly! I really appreciate it!!