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".
Why is this different/better to using COUNTIF?
ReplyDeleteeg (in row n) IF($An="North",COUNTIF($A$1:$An,$An),"")
This seems much simpler, isn't sequential (ie each calc stands alone rather than being dependant on all the previous ones) and is more easily extended to numbering the occurrences of all entries (ie just skip the IF)
Jim
Jim
DeleteThanks for the input. That's one of the great things about Excel - there's always three or four ways to do something. I think both approaches are on a par. They're both a similar level of complexity (it depends whether you're more comfortable with COUNTIF or MAX - MAX has less arguments, but you've got to add one to it). For this problem, I don't think the sequential thing is really an issue as, the purpose of the exercise is to create a sequence. I don't know if either approach can claim any advantages regarding efficiency. I'd say use whichever approach appeals to you!
Thanks Glen. I noticed in my testing that I had to have a blank row above the formula to return a result in the first cell (i.e. B2) if the first cell should have a 1. I created a quick list containing north, south, east west alternating and entered your formula looking for north. When I started the list in A1 the formula returned 0 in B1 and all subsequent results below were 0. However, when I inserted a blank row at Row 1, the formula then worked all the way down.
ReplyDeleteCarlM
Good point Carl. Most situations in which you would use this would have a header row, so as long as your header wasn't a number you'd be fine too.
DeleteHi, inspired by your article (found on linkedin) I've created another formula that can be usefull, instead of counting only 1 value you can count all of them by value
ReplyDeleteTry to create a list of values in the column A starting from row 2 with some of them that are repeating, then in the cell B2 put this formula:
=SUM.PRODUCT(($A$1:$A1=A2)*1)+1
The result will be
North 1
West 1
North 2
South 1
West 2
West 3
North 3
East 1
South 2
Hope you like it and sorry for my english :D
Ciao Gianfranco,
DeleteI like it, and your English is perfect I think, except for the fact that useful is with only 1 f.
I wih i could write in Italiano si bene que tu.
Salute, Daniele
Or more simply in cell Bn:
ReplyDelete=COUNTIF($A$1:$An,$An)
True :)
Delete