Filter Problems

Hi all was hoping someone could give me some incite on how I might be able to work this problem out. I currently have a list of names in Col. C and 3 digit test scores in Col. G. However some names are repeated as a person can take a test multiple times.

Example

C. G.
1. Michael 500
2. Michael 510
3. Gary 920
4. Gary 920
5. Gary 960
6. Joan 800
7. Joan 740
8. Eric 160
6. Jenn 410
7. Jenn 410

I need a way to convert that list into this:

Michael 510
Gary 960
Joan 800
Eric 160
Jenn 410

Where each name is only displayed once, and the max test score is next to it.

Any help is greatly appreciated.

Thanks

Hi Gorf,

I broke your problem up into two parts. First you need a list of unique names and secondly, you need to find the maximum score for each name.

Part 1
Highlight your list of names (in my spreadsheet, column A. You can include the heading).

Go to Data > Filter > Advanced Filter
Choose "Copy this List to another location"
Leave Criteria Range Blank
Place your cursor in the Copy to Box and click on where you want it copied to (D1 in my spreadsheet)
Click OK. You should get a unique list in Column D.

Part 2
Starting in cell E2, create an array formula

Please Login or Register  to view this content.

An Array formula must be entered using CNTRL SHFT Enter (instead of a simple ENTER). This places Brackets {} around your formula. Drag that formula down as far as you want.

Explaination
The IF(ISTEXT(D2) part looks at column D and only calculates a value if there is a name there, otherwise it puts a blank ("")

MAX(...) looks for the maximum value stored by the array which multiplies

Please Login or Register  to view this content.

times

Please Login or Register  to view this content.

where the first part will be either 0 or 1 depending on whether the expression is true or false and the second part will be the score.

ChemistB

Wow that worked perfectly, thank you so much!! Was a real time saver. I appreciate the help and timely response.

Thanks

Glad I could help and thanks for the feedback. Often I'm left wondering whether what I said worked or was even tried.

ChemistB