Suppose the data source is in sheet 1 and the target location is in sheet2. First, do some advanced filtering:

Select to copy the filter results to another location.

List area: sheet 1! A: B.

Conditional area: click a blank cell at will.

Copy to: sheet2! a 1

Select "Select non-duplicate records"

of course

Then fill in c2 of sheet2.

=SUMIF(sheet 1！ A: A, A2, sheet 1! C: C)

Fill down

Fill in d2 in Table 2.

=MAX(IF(sheet 1！ A=A2, sheet 1! :D)

Press Ctrl+Shift+Enter at the same time.

Fill down