MikeAu
03-07-2019, 08:20 AM
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
03-07-2019, 09:28 AM
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
03-07-2019, 01:57 PM
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
03-07-2019, 03:31 PM
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
03-08-2019, 09:40 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.