Script to - subtotal and remove duplicates with lower subtotal

[derek.hughes]

This sheet has thousands of rows of data, but this is a "snippet" of what I am trying to accomplish.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Bargain Unit[/TD]
[TD="align: center"]Pay Class[/TD]
[TD="align: center"]FTE[/TD]
[/TR]
[TR]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]220[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0.05[/TD]
[/TR]
[TR]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]220[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0.10[/TD]
[/TR]
[TR]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]220[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0.10[/TD]
[/TR]
[TR]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]220[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]0.50[/TD]
[/TR]
[TR]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]220[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]0.25[/TD]
[/TR]
</tbody>[/TABLE]

I have employees that work in the same bargaining units, but under different pay classes. I essentially want a script that will:

• Figure out the Sum of the FTE, by Pay Class (Subtotal)
• Remove duplicates, leaving behind the rows with the LARGEST SUM of FTE

In the example above it would leave behind the last two rows - by the way I do not need it to leave behind the summed total of the FTE, I just used that to explain how I want the Macro to decide which rows to remove.

Thanks!

Derek

 

[venkat1926]

COPY DATA IN SHEET 1 TO SHEET 2 TO PRESEVE ORIGINAL CONFIGURATION


for subtotal problem the following macrto will work

Sub test()
Dim rdata As Range
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
Worksheets("sheet1").Activate
Set rdata = Range("A1").CurrentRegion
    rdata.RemoveSubtotal
    rdata.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
            
End Sub

but "remove duplicate leaving behid te rows with largest sum of FTE" this is not clear. how the last two rows give the largest sum (the operate word"sum" is cofusig").if individual FTE is considered fouth row to be retained.
assuing fourth row only is retained then presume original subtotal of 1.0 is to be retained.


explian with examples