Excel Auto number row based on criteria

Question

I'm trying to create a spreadsheet in excel which creates a sequential number in a column (B) depending on the contents of another column. Currently, there are two possibilities of what could be in Column A ("BI" or "GF"). So I want the data to look like this

    COL A    COLB
     BI       1
     BI       2
     GF       1
     BI       3
     GF       2
     GF       3
     BI       4
     BI       5

I've tried several attempts to do this but can't seem to find a solution. Any help would be greatly appreciated.

Solution

In B2, try this formula: =CountIf(A$2:A2,A2)

Comments

[user2631154]

So simple yet I just couldn't see it, works a treat, thanks!