Counting the number of times a cell value changes

cherias

New Member
Joined
Sep 9, 2015
Messages
15
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.

ABCD
Detected DateTarget DateActual DateTime Change Counter
19-Jan-1923-Feb-194-Mar-19Need incremental counter when value changes in column C<need incremental="" counter="" for="" changed="" to="" cell="" in="" c=""></need>

<tbody>
</tbody>

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
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Sep 9, 2015
Messages
15
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

Well-known Member
Joined
Apr 11, 2012
Messages
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,592
Messages
5,838,253
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top