[SOLVED] In a dataset where individuals have multiple rows of data per enrollment, how can I make a variable that indicates enrollment instance (1st/2nd/etc.)?

In a dataset where individuals have multiple rows of data per enrollment, how can I make a variable that indicates enrollment instance (1st/2nd/etc.)?

Here's some background information on the dataset I'm working with in SPSS (I'm open to solutions in Excel as well):

1.) Individuals have multiple rows of data

2.) Individuals may have enrolled in a certain program more than once, giving them a new value for the "Item" variable shown below

My goal is to create a variable like the one called "Enrollment" in the example below, which uses "Item" to tell if a row is from the individual's first/second/etc. enrollment in the program.

Picture of Variables

I tried:

1.) Computing obsnum, but that understandably just resulted in a sequential order.

2.) Identifying duplicate cases but that just gave the first instance a 1 and the others 0s (also understandable).

3.) Ranking ID by item, and I expected values like 1, 2, etc., but got things like 4.500 and 2.500. I don't know what that means, though admittedly I had never done anything like this; I was just trying an answer from another question.

I hope this makes sense, and I'm happy to clarify if it doesn't. Thank you so much for your time and thoughts!

Solution

One way to do this is using the lag function. First you need to sort the cases to make sure they are numbered correctly, then calculate enrollment by checking for each new row of the same ID if the item has changed - if it did we add 1 to the number of enrollment:

sort cases by ID Item.
compute enrollment=1.
if $casenum>1 and ID=lag(ID) enrollment=(lag(enrollment) + (Item<>lag(Item))).
exe.