tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,825
- 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):
Group Field1 Field2 Field3 Field4 1 a aa aaa 1 1 a w aaa 2 1 a aa aaa 3 2 d dd ddd 5 2 e ee eee 8 2 d dd ddd 88 3 g gg ggg 878 3 h hh hhh 78 3 h hh hhh 5435
<colgroup><col span="5"></colgroup><tbody>
</tbody>
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I am trying to get this result:
Rich (BB code):
Group Field1 Field2 Field3 Field4 1 a aa aaa 4 1 a w aaa 2 2 d dd ddd 13 2 e ee eee 8 3 g gg ggg 878 3 h hh hhh 5513
<colgroup><col span="5"></colgroup><tbody>
</tbody>
[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>