case counter in Excel 2007?

**mistertwofish** · 09-10-2012, 05:53 PM

Hi all. New member with what I suspect is a fairly old problem (but could not find via keyword search)...

I have been tasked with creating a case counter variable in a very large spreadsheet (5000+ rows) that contains data at two levels -- people and cases. Each person in the file can have multiple cases of data (in separate rows), but the majority of the people only have a single case (row). For the purposes of this project, the Case counter should only show a value for the persons who have multiple cases. Otherwise, the case cell should be blank. Example below...

Person Case
1
2
3
4 1
4 2
5
6
7
8
9 1
9 2
9 3
9 4
10
11
12

To verbalize the above: Person #4 has two cases, so the first case is labelled as case 1 and the second case is labelled as case 2. Person #9 has four cases, so they are labelled 1,2,3,4 in order to differentiate each of the cases for that person from each other.

The method that I am looking for seems to deviate a bit from the standard use of the COUNT functions (since I don't just want a total case count for each person but rather something that looks more like a running total that resets each time a new person is found). I have been fumbling around with the MATCH and VLOOKUP functions trying to piece together a custom formula that could be copied down in the case column, but am struggling to put the whole thing together. Frankly, I'm not even sure this is the correct forum for this post, but I know you will tell me to move my thread if it isn't.

Thanks for your time and support.