In a list of Code-Group-Member-value Find out max value for each member in a group

Dear All,

I have some set of data in four column - "A" , "B" , "C" , "D"
Column "A" is code
Column "B" is Group (Set of similar code)
Column "C" is members of code
Column "D" is points

There are equal member against code for specific group.
I required a list of code -group-member-max point for all group.

To solve this at present I filter each group, then filter each member and in
Column "E" - "FILTER FOR MAX POINT". I put "1" against max value.
After that I filter Column "E" for value "1". I copy this data to other
worksheet and filter member in ascending order. Again copy the same and paste it in final result.
I manually combine code -for example AC28,AC27,AC32,AC31. (Please refer attached file).

For demonstration I have considered 4 group in attached file. They are " G1", "G12", "G13" , "G16".


I need help to automate the procedure as I have long list of data. Is it required to write macro to solve the issue?

Kindly suggest solution.

Regards,
PN

Dear JieJenn,

Thanks for your kind reply. I know very little about Pivot table.Yes will help me to find out max value. But I need to use this data again for final report. So I need data under column head only.

We can alter column here and there.

Regards,
PN

Suppose we forget Code column ( AC4,AC1,AC3,AC7 etc) in result. I will put it manually. After that can we get required result?
Regards,
PN

Minor error in Required result. I am attaching revise file.
Regards,
PN