Counting the number of times a cell value changes

[cherias]

Hello All,

I referred to the earlier post from 2004 but didn't post my queries due to aging of the thread but my queries as same, so please help .

Earlier Post : https://www.mrexcel.com/forum/excel-questions/67250-counting-number-times-cell-value-changes.html

I tried using the VBA given in the link, but it didn't work in my case.

[TABLE="width: 363"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Detected Date[/TD]
[TD="align: center"]Target Date[/TD]
[TD="align: center"]Actual Date[/TD]
[TD="align: center"]Time Change Counter[/TD]
[/TR]
[TR]
[TD="align: center"]19-Jan-19[/TD]
[TD="align: center"]23-Feb-19[/TD]
[TD="align: center"]4-Mar-19[/TD]
[TD="align: center"]Need incremental counter when value changes in column C<need incremental="" counter="" for="" changed="" to="" cell="" in="" c=""></need>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please assist.

I used the below codes posted by Derek.

Different approach:

Paste this as the worksheet event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then Target.Offset(0, 1).Value = 1
End Sub


Paste this in ThisWorkBook:

Private Sub Workbook_Open()
Columns("B:B").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
, Transpose:=False
Application.CutCopyMode = False
Columns("B:B").ClearContents
End Sub

 

[mumps]

Is the change in column C made manually or is it the result of a formula? What is the last column containing data in your sheet?

 

[cherias]

Hello Mumps,
Query (1) -

The data (dates) in column C is changed manually. The last column of the sheet is "Comments/Remarks". Basically, the sheet is used track the progress of risk remediation. The detected date is provided by Vendor when a new risk is identified and the target date is set based on initial assessment by technical team, to project by when the risk will be remediated.

Adding additional information & query (2) -

One catch is that the risk may initially be identified on only few devices, say 20, and while planning and action may be in progress, it may happen that same risk is identified on another set of devices. E.g. Risk A was identified on 14 Feb on devices (different S.No #1 ,2,3,5,6,7,8) and the team starts to get it remediated. Let's say by 03 Mar, Risk A is identified on another set of devices (different S.No # 9,10,11,12,13). So while the action is on progress on first set of devices, we get another set of devices for same risk, therefore increasing total number of risks but will different detected date. I was planning to use burn down chart but confused on how it turn out due to running data.

 

[mumps]

I think that to understand fully what you want to do and to test possible solutions, it would be easier if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.