GBKEE

¨Ï¥Î°}¦C¤è¦¡
Sub Ex()
    Dim Ar(), M, E As Range, R
    ReDim Preserve Ar(1 To 1)
    Ar(1) = Array("ref no", "Sum of ctr", "Sum of gw", "remark")
    M = Array(Ar(1)(0))
    For Each E In Sheet1.Range("A2", Sheet1.[d65536].End(xlUp)).Rows
        R = Application.Match(E.Cells(1, 1).Value, M, 0)
        If IsNumeric(R) Then
            Ar(R)(1, 2) = Ar(R)(1, 2) + E.Cells(1, 2)
            Ar(R)(1, 3) = Ar(R)(1, 3) + E.Cells(1, 3)
        Else
            ReDim Preserve Ar(1 To UBound(Ar) + 1)
            Ar(UBound(Ar)) = E.Value
            M = Split(Join(M, ",") & "," & Ar(UBound(Ar))(1, 1), ",")
        End If
    Next
    Sheet2.Columns("A:D") = ""
    Sheet2.[a1].Resize(UBound(Ar), 4).Value = Application.Transpose(Application.Transpose(Ar))
End Sub