Simple counter down column

Hi All,

I am looking for a simple counter that counts every time a cell changes in Column B.

Examples:

If B10 changes then I would like to have a "1" in G10.  If B10 changes again then a "2" is in G10 and so on.

Similarity, if B23 changes then I would like to have a "1" in G23.  If B23 then changes again 3 more times then G23 should equal 4.

...and so on for each relevant row down the column.

If the corresponding cell in "I" becomes a "1" then the counter should reset to zero and if there is "" in the same cell then the counter is "ready" to start counting again.

I have included a sample sheet to get going on this.

thanks.
counter.xlsm
BostonBobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Not sure what yo meant by "If the corresponding cell in "I" becomes a "1" then the counter should reset to zero and if there is "" in the same cell then the counter is "ready" to start counting again. " so currently my code doesn't accommodate that.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
        Range("G" & Target.Row) = Range("G" & Target.Row).Value + 1
    End If
End Sub

Open in new window


Put the code in the worksheet module.
0
Rgonzo1971Commented:
HI,

You could do it with this formula ( the first cell should have a zero no formula)

=IF(J11=1,0,IF(B11<>B10,H10+1,H10))

Open in new window


if you only want to see the change number once use conditional formatting like in the example

Regards
counterV1.xlsm
0
BostonBobAuthor Commented:
MacroShadow,

Thanks for that.  To answer your question column "I" + relative row should be used to reset the count back to zero.  If it can be reset by another way then please let me know.  This is all going into an automated program so I need some way to do that.

Rgonzo1971

Thanks for that.  Not sure if this does the job. The counter increases as we go from low rows to higher rows.  The counts for each row is independent of the other.

thanks!
0
MacroShadowCommented:
Ok. Thanks for the clarification.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
        Select Case Range("I" & Target.Row).Value
            Case Is = ""
                Range("G" & Target.Row) = Range("G" & Target.Row).Value + 1
            Case Is = "1"
                Range("G" & Target.Row) = ""
        End Select
    End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BostonBobAuthor Commented:
Beautiful!  Thanks!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.