Counting/Restart Count based on another column

[BryanNeff]

Hey all! Another question trying to make my life a little easier when converting databases.

Column A is existing information - Column B is the information I need created via formula.

In Column A, I have a series of 1’s, then a series of 2’s, etc. The number of 1’s, 2’s, etc can vary on the DB.

I need a formula for column B to number these from 1-x where x is the last before the start of of 2 in A.

For example:

|A||B|

|1||1|

|1||2|

|1||3|

|2||1|

|2||2|

|2||3|

|2||4|

|3||1|

|3||2|

etc.

[BasicMacro]

This only works if the numbers in column A are sorted.

• Put 1 in cell B1 as this cell will never change.
• In B2 put this formula.

=IF(A1=A2,B1+1,1)

How it works,

IF A1 = A2 THEN add 1 to the previous value in column B, ELSE reset the counter to 1.

[BryanNeff]

That works! Thank you.