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

Question

I have two columns: one for image URLs and one for "Image Position." I would like the Position column to increase by 1 if the picture URL has the same "Base ID" because of this. I want to reset everything back to 1 if the Base value changes.

As an illustration, all files end with foobar-8.jpg. I require a method to determine whether Foobar modifies and, if so, whether it restarts at 1. Otherwise, keep adding a +1 as seen below. Is VBA able to handle this? I'm unable to determine using standard Excel (2013).

Answer 1

Use COUNTIF:

=COUNTIF(A$2:A2,A2) 

The absolute and relative cell coordinates are what you should focus on in the formula I proposed. The formula becomes: =COUNTIF(A$2:A7,A7), which means it counts all occurrences of the value in A7 in all the preceding rows, as A$2 remains constant as you populate it down and A2 increments up.