CODEWITHSUNDEEP
excelvba
nahsn
nahsn

Reputation: 13

How to create a VBA loop to sum totals based on an unknown number of repeating values in a column?

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

Answers (1)

Menelaos
Menelaos

Reputation: 26609

Final Result

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:

  • A2 is the first cell containing Val labels (Val1, Val2, ... , ValN)
  • B2 is the first cell containing Numerical Values

enter image description here

enter image description here

Explanation:

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.

Step by Step

enter image description here

enter image description here

Additional Entries (Repeat Values)

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.

enter image description here

Upvotes: 1

Related Questions