Code to know when values in column changes

[MikeAu]

In my Excel Worksheet I have a lot of rows (around 65000 average) and those rows contains either 1 , 0 or UNDEF and I would like a code to know how many time the value changes. Example: For column... it changes 15 times from 1 to 0 and 10 times from UNDEF to 1 etc. I've tried stuff but nothing is close to what I want so far.

Thank you

[Paul_Hossler]

What makes it change?

By individual cell, entire column, or what?

Count each type of change? i.e. 0 --> 1, 1 --> UNDEF, 0 --> UNDEF, 1 --> 0, …. There are 6 permutations

How do you want to know, seperate column, message box, .....?

Attach a small sample workbook showing the data, and how you think you want the answer

[MikeAu]

Thanks for responding!
There is an example of what I have attached to this post.
You'll see in the column AGI_ALARM the values in cells go from undef to 1 (row 13797) then back to undef (row 13825). Again at row 14026 it switch from undef to 1 and back to undef at row 14039. If we just use that (of course you'll see there is way more data, but lets say we only have that for my example) what I would like to know is: undef to 1 2 times (13797,14026) 1 to undef 2 times (13825,14039).
I don't really care how I get the infos as long as i can understand.

Thank you

Mike



23856

[Paul_Hossler]

I think I understand -- play with the attachment

Here's the macro

The transition counts are captured in the N array, and there's a message at the end

My number of Undef-->1 was 3 not 2 so I added a 'log' worksheet "Transitions" to check

23857





Option Explicit
Const Value0 As Long = 0
Const Value1 As Long = 1
Const ValueUndef As Long = 2

Sub Changes()
Dim wsTransitions As Worksheet
Dim rData As Range
Dim i As Long, iOut As Long
Dim N(Value0 To ValueUndef, Value0 To ValueUndef) As Long ' row = i-th, column = i+1-th
Dim sMsg As String

Set rData = Intersect(ActiveCell.EntireColumn, ActiveSheet.UsedRange)

Call rData.Replace("Undef", 2, xlWhole)

iOut = 1
Set wsTransitions = Worksheets("Transitions")
With wsTransitions
.Cells(1, 1).CurrentRegion.ClearContents
.Cells(iOut, 1).Value = "Prev Row#"
.Cells(iOut, 2).Value = "Prev Value"
.Cells(iOut, 3).Value = "Next Row#"
.Cells(iOut, 4).Value = "Next Value"
iOut = iOut + 1
End With


With rData
For i = 2 To .Rows.Count - 1
N(.Cells(i).Value, .Cells(i + 1).Value) = N(.Cells(i).Value, .Cells(i + 1).Value) + 1

If .Cells(i).Value <> .Cells(i + 1).Value Then
wsTransitions.Cells(iOut, 1) = i
wsTransitions.Cells(iOut, 2) = .Cells(i).Value
wsTransitions.Cells(iOut, 3) = i + 1
wsTransitions.Cells(iOut, 4) = .Cells(i + 1).Value
iOut = iOut + 1
End If

Next i
End With

Call rData.Replace(2, "Undef", xlWhole)
Call wsTransitions.Columns(2).Replace(2, "Undef", xlWhole)
Call wsTransitions.Columns(4).Replace(2, "Undef", xlWhole)

sMsg = "Transition Counts" & vbCrLf & vbCrLf
sMsg = sMsg & "Transition 0-->1 = " & Format(N(0, 1), "#,##0") & vbCrLf
sMsg = sMsg & "Transition 0-->Undef = " & Format(N(0, 2), "#,##0") & vbCrLf
sMsg = sMsg & "Transition 1-->0 = " & Format(N(1, 0), "#,##0") & vbCrLf
sMsg = sMsg & "Transition 1-->Undef = " & Format(N(1, 2), "#,##0") & vbCrLf
sMsg = sMsg & "Transition Undef-->0 = " & Format(N(2, 0), "#,##0") & vbCrLf
sMsg = sMsg & "Transition Undef-->1 = " & Format(N(2, 1), "#,##0") & vbCrLf & vbCrLf

sMsg = sMsg & "Unchanged counts" & vbCrLf & vbCrLf
sMsg = sMsg & "Transition 0-->0 = " & Format(N(0, 0), "#,##0") & vbCrLf
sMsg = sMsg & "Transition 1-->1 = " & Format(N(1, 1), "#,##0") & vbCrLf
sMsg = sMsg & "Transition Undef-->Undef = " & Format(N(2, 2), "#,##0") & vbCrLf

MsgBox sMsg
End Sub

[MikeAu]

Hi Paul

Great work' it works perfectly. I think I will just change the row number by the TimeStr for easier analysis.

Thanks again for your time

Mike