Ask Your Question
0

Counting/Restart Count based on another column [closed]

asked 2014-03-10 15:26:50 +0100

Bryan Neff gravatar image

updated 2014-03-10 15:45:13 +0100

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 07:10:33.939856

1 Answer

Sort by » oldest newest most voted
0

answered 2014-03-10 18:42:22 +0100

BasicMacro gravatar image

updated 2014-03-10 18:44:31 +0100

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.

edit flag offensive delete link more

Comments

That works! Thank you.

Bryan Neff gravatar imageBryan Neff ( 2014-03-10 19:13:32 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2014-03-10 15:26:50 +0100

Seen: 66 times

Last updated: Mar 10 '14