Option Explicit
Sub test()
Dim arr, i, j, k, t, p, m
arr = Range("h3:j" & Cells(Rows.Count, "h").End(xlUp).Row)
ReDim brr(UBound(arr, 1) / 2, 4)
For i = 1 To UBound(arr, 1)
If Left(arr(i, 1), 5) = "Group" Then
p = i
ElseIf Len(arr(i, 1)) Then
m = m + 1
brr(m, 0) = arr(i, 1)
brr(m, Val(Mid(arr(p, 2), 2))) = arr(i, 2)
brr(m, Val(Mid(arr(p, 3), 2))) = arr(i, 3)
End If
Next
brr(0, 0) = "Sum"
For i = 1 To 4
brr(0, i) = "C" & i
Next
For i = 1 To m - 1
For j = i + 1 To m
If brr(i, 0) > brr(j, 0) Then
For k = 0 To UBound(brr, 2)
t = brr(i, k): brr(i, k) = brr(j, k): brr(j, k) = t
Next
End If
Next
Next
[b3].Resize(m + 1, UBound(brr, 1) + 1) = brr
End Sub |