Well, this is one of those times where the easiest solution is to use the Java Snippet node. I generally hesitate to provide solutions to others using the Java Snippet node because many times customers aren't Java users, and I prefer to show them it can be done without using Java. Perhaps there's a clever way to avoid the use of Java Snippet, but I don't have enough time to think of it today.
Please see the attached workflow. In a nutshell, the Snippet declares a 'global' variable that keeps track of the current observed count for each value in a data structure called a Map. For each row, the map is consulted for the last observed count for the value in that row and then the new 'Sequential Occurrence' column is populated with the count value plus 1.
By the way, if it's okay instead to summarize the count by value (i.e. each value gets its own row), you should check out the GroupBy node.