Conditionally delete duplicates

[kweidle]

Good afternoon!

The practical background: I work in education and am trying to calculate pass rates. Sometimes students are allowed to retake a test if they fail, but my .csv download from the software lists all their scores, and I only want the highest score.

What I want Excel to do: If student ID number (Column E) and Subject Code match (column D), only keep the highest score.
*I have placed and "Expected results" example in the Excel document

I have attached a sample spreadsheet here. The actual file I am working with has over 50,000 rows so I want to work smarter and not harder. Hopefully there is a formula that can help me! Possibly a variation of a VLOOKUP (which I have never used).

I am an excel forum newbie so please let me know if you have any questions. I absolutely appreciate the help!

[oeldere]

You get better help on your question if you add manualy the expected result in the excel file and post it again.

[kweidle]

Thank you very much oeldere! I have updated the excel document with expected results.

[oeldere]

Here a solution with a pivot table.

That is the way i should solve it.

I will take a look to get the result, like you asked.

[oeldere]

See the attached file.

I used helpcolumn to get the result.

I filtered on blanc and 2.

[kweidle]

That worked out exactly the way I need it!

I do have one question, where did the G20 come from? It was the only constant in the formal (I know the rest changed based on the row #). If I have 30,000 rows should that number by 30,000? I just want to make sure that my first equation is correct because I plan on just copying it down the column.

=IF(COUNTIF($G$2:$G$20,$G2)=1,"",COUNTIF($G$2:$G2,$G2))

Thank you for your continued help!

[oeldere]

Yes that number should be 30.000.

But I am afraid the file will collapse, since it is a lot of work for excel.

I would try to use a smaller range.

After the formula has been excecuted, copy => paste special values the range.

[kweidle]

So far my Excel file has not crashed and I am extremely thankful for your help! You were quick to reply and helped so much!