From the Not Just Numbers blog:
There are a number of situations, where it is useful to number each occurrence in a list.
We could place a flag in column B next to each occurrence using an IF statement, e.g.
=IF(A2=”North”,”Yes”,””)
Copying this down would place a Yes in column B next to each occurrence of North in column A, but it would not distinguish between each occurrence – they would all say Yes.
Fortunately there is a simple tip that you can use to place a number in column B, so that the first occurrence would show 1, the second 2, etc.
The principle is the same, we just need to replace “Yes” with something a little cleverer.
What we actually want now is to add 1 each time to the highest number already showing.
We can use the MAX function to find this highest number.
If we are again entering the formula in cell B2:
=MAX($B$1:$B1)
will return the highest number in the rows above (i.e. in cell B1).
Notice, though, the dollar signs. I have fixed both row and column on the start of the range, but only fixed the column on the end of the range. So copying this down, the end of the range would move, always going down as far as the cell above.
We can then use this formula, and add one, in place of “Yes” in our original:
=IF(A2=”North”,MAX($B$1:$B1)+1,””)
Whenever North appears in column A, this will find the maximum value in column B above this cell, and add 1 to it.
So, the first time North appears, there will be nothing above the row in column B, so 0+1=1. This will therefore return a 1 in column B.
Next time North appears, the maximum value in column B above this cell, will be 1 (returned against the first occurrence), so 1+1=2, etc.
We have all of our occurrences numbered in sequence now!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.