CODEWITHSUNDEEP
excelvba
HelloWorld
HelloWorld

Reputation: 111

How to increment a column value if it repeats, otherwise reset to 1?

I have a column of image URL's and other column is "Image Position". So if the image URL has the same "Base ID" I would like Position column to increment from 1. If the Base value differs, I'd like to reset back to 1.

For example (you see all end with foobar-8.jpg .. I need way to check if foobar changes and if it does start back at 1. Otherwise keep incrementing +1 like below? Is this doable with VBA? I can't figure out with regular Excel (2013).

enter image description here

Upvotes: 0

Views: 551

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

Use COUNTIF:

=COUNTIF(A$2:A2,A2)

The thing to pay attention to in the formula I suggested is the absolute and relative cell coordinates. As you populate it down, A$2 stays the same and A2 increments up, so 5 rows down, the formula becomes: =COUNTIF(A$2:A7,A7) which means is counts all instances of the value in A7 in all the previous rows

An explanation of COUNTIF can be found here:

https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34

Upvotes: 1

Related Questions