tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,926
- Office Version
- 365
- 2019
- Platform
- Windows
I am trying to sum rows based on some criteria.
(The Group column is only here to explain that the rows come in "3s").
This is the data:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I am trying to get this result:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]because if Field1, Field2 and Field3 match, then sum Field4 (but only within the same group).
I have tried this code:
[/FONT]
but it doesn't work because if rows 1 and 3 are the same, they are summed (as it should) and row 2 also gets "picked" up by OutputArray but row 3 gets "counted" again.
How can I amend it to get it to work?
Thanks
[/FONT]<strike>
</strike>[/FONT]<strike></strike>
(The Group column is only here to explain that the rows come in "3s").
This is the data:
Rich (BB code):
[TABLE="width: 225"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Group[/TD]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Field3[/TD]
[TD]Field4[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]a[/TD]
[TD]aa[/TD]
[TD]aaa[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]a[/TD]
[TD]w[/TD]
[TD]aaa[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]a[/TD]
[TD]aa[/TD]
[TD]aaa[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]d[/TD]
[TD]dd[/TD]
[TD]ddd[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]e[/TD]
[TD]ee[/TD]
[TD]eee[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]d[/TD]
[TD]dd[/TD]
[TD]ddd[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD]g[/TD]
[TD]gg[/TD]
[TD]ggg[/TD]
[TD="align: right"]878[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD]h[/TD]
[TD]hh[/TD]
[TD]hhh[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD]h[/TD]
[TD]hh[/TD]
[TD]hhh[/TD]
[TD="align: right"]5435[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I am trying to get this result:
Rich (BB code):
[TABLE="width: 225"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Group[/TD]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Field3[/TD]
[TD]Field4[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]a[/TD]
[TD]aa[/TD]
[TD]aaa[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]a[/TD]
[TD]w[/TD]
[TD]aaa[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]d[/TD]
[TD]dd[/TD]
[TD]ddd[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD]e[/TD]
[TD]ee[/TD]
[TD]eee[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD]g[/TD]
[TD]gg[/TD]
[TD]ggg[/TD]
[TD="align: right"]878[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD]h[/TD]
[TD]hh[/TD]
[TD]hhh[/TD]
[TD="align: right"]5513
[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]because if Field1, Field2 and Field3 match, then sum Field4 (but only within the same group).
I have tried this code:
Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim DataArray() As Variant
DataArray() = Cells(1, 1).CurrentRegion.Value
Dim OutputArray(1 To 10, 1 To 4) As Variant
Dim i
Dim j
Dim k
k = 1
For i = 2 To 10
If DataArray(i, 1) = DataArray(i + 1, 1) And _
DataArray(i, 2) = DataArray(i + 1, 2) And _
DataArray(i, 3) = DataArray(i + 1, 3) Then
For j = 1 To 3
OutputArray(k, j) = DataArray(i, j)
Next j
OutputArray(k, 4) = DataArray(i, 4) + DataArray(i + 1, 4)
If DataArray(i, 1) = DataArray(i + 2, 1) And _
DataArray(i, 2) = DataArray(i + 2, 2) And _
DataArray(i, 3) = DataArray(i + 2, 3) Then
OutputArray(k, 4) = OutputArray(k, 4) + DataArray(i + 2, 4)
i = i + 2
Else
i = i + 1
End If
Else
If DataArray(i, 1) = DataArray(i + 2, 1) And _
DataArray(i, 2) = DataArray(i + 2, 2) And _
DataArray(i, 3) = DataArray(i + 2, 3) Then
For j = 1 To 3
OutputArray(k, j) = DataArray(i, j)
Next j
OutputArray(k, 4) = DataArray(i, 4) + DataArray(i + 2, 4)
Else
For j = 1 To 4
OutputArray(k, j) = DataArray(i, j)
Next j
End If
End If
k = k + 1
Next i[/FONT]
but it doesn't work because if rows 1 and 3 are the same, they are summed (as it should) and row 2 also gets "picked" up by OutputArray but row 3 gets "counted" again.
How can I amend it to get it to work?
Thanks
[/FONT]<strike>
</strike>[/FONT]<strike></strike>