Reputation: 13
I have a data set where some some values (VAL) repeat and some do not, an unknown number of times. Each VAL is associated with a SCORE (0 to 5).
I am writing a VBA loop to start with the first VAL and search the rest of the rows in the same column. If the VAL repeats, I want to total the SCORE for those IDs. The sum, however, should only appear in the cell in the same row as the LAST repeated VAL.
I managed to start this, but I'm not sure how to edit so it searches for ALL identical VALs, and not just the one above or below it. I posted my code as well as a sample of the desired results.
For Row = 2 To 350
If Cells(Row, 1) = Cells(Row + 1, 1) Then
Cells(Row, 6) = (Cells(Row, 4)) + (Cells(Row - 1, 4))
Else
Cells(Row, 6) = Cells(Row, 4)
End If
Next
End Sub
For example, if VAL1 has a score of 1 and does not repeat, the total should be 1. If VAL2 has a score of 0 and does not repeat, the total should be 0. If VAL3 repeats 3 times and has scores of 0, 1, 1, the total should be 2, but 2 should appear only in the 3rd cell - the 2 cells before it should be 0.
VAL1 1 1
VAL2 0 0
VAL3 0 0
VAL3 1 0
VAL3 1 2
VAL5 0 0
VAL5 1 0
VAL5 1 0
VAL5 1 3
Thank you in advance
Upvotes: 0
Views: 183
Reputation: 26609
Please see the following formulas that step by step solve this problem.
These can also be combined into one large formula that takes care of the problem in one shot.
That formula is:
=IF( COUNTIF(A2:$A$1048576,A2)-1 = 0, SUMIFS($B$2:$B$1048576,$A$2:$A$1048576,A2), 0 )
Where:
The countIf
condition counts the number of times the specific value appears after the current row. If that is equal to zero, it means it is the last Value.
The second SumIfs
, sums the total numerical value for the ValN label.
Additionally, the above formula deals with values that repeat again but are not consecutive. E.g. Another entry of VAL1 or VAL5 that came at the end.
Upvotes: 1