Capturing data only when a column "Value" changes

[ShagNasty]

What I need to accomplish is to get three items; tag, time, and value,
automatically copied into a “clean†worksheet every time the Widgets value
changes -- ON/OFF. As you can see from my mess below, I ain’t got the
faintest cue what I’m doing….

Phase 1
I retrieve history on “Tagâ€, “Timeâ€, and “Value†every minute during a
startup/shutdown process event – I may have to go back 30 days. Raw data is
exported from a large db. I probably have 90 columns with around 1000 rows

Tag Time Value Tag Time Value
Widget A 09/30/2008 11:55:00 ON Widget B 09/30/2008 11:55:00 ON
Widget A 09/30/2008 11:56:00 ON Widget B 09/30/2008 11:56:00 ON
Widget A 09/30/2008 11:57:00 OFF Widget B 09/30/2008 11:57:00 ON
Widget A 09/30/2008 11:58:00 OFF Widget B 09/30/2008 11:58:00 OFF
Widget A 09/30/2008 11:59:00 OFF Widget B 09/30/2008 11:59:00 OFF
Widget A 10/01/2008 00:00:00 OFF Widget B 10/01/2008 00:00:00
Widget A 10/01/2008 00:01:00 ON Widget B 10/01/2008 00:01:00
Widget A 10/01/2008 00:02:00 OFF Widget B 10/01/2008 00:02:00 ON
Widget A 10/01/2008 00:03:00 ON Widget B 10/01/2008 00:03:00 ON
Widget A 10/01/2008 00:04:00 ON Widget B 10/01/2008 00:04:00 ON
Widget A 10/01/2008 00:05:00 OFF Widget B 10/01/2008 00:05:00 OFF
Widget A 10/01/2008 00:06:00 ON Widget B 10/01/2008 00:06:00 ON
Widget A 10/01/2008 00:07:00 ON Widget B 10/01/2008 00:07:00 ON
Widget A 10/01/2008 00:08:00 Widget B 10/01/2008 00:08:00
Widget A 10/01/2008 00:09:00 Widget B 10/01/2008 00:09:00
Widget A 10/01/2008 00:10:00 ON Widget B 10/01/2008 00:10:00 ON
Widget A 10/01/2008 00:11:00 OFF Widget B 10/01/2008 00:11:00 OFF
Widget A 10/01/2008 00:12:00 ON Widget B 10/01/2008 00:12:00 OFF
Widget A 10/01/2008 00:13:00 ON Widget B 10/01/2008 00:13:00 OFF
Widget A 10/01/2008 00:14:00 OFF Widget B 10/01/2008 00:14:00 ON
Widget A 10/01/2008 00:15:00 OFF Widget B 10/01/2008 00:15:00 OFF
Widget A 10/01/2008 00:16:00 ON Widget B 10/01/2008 00:16:00 ON

-----------------------------------------------------------------------------------------------------------------------------------------------
Phase 2

Tag Time Value Tag Time Value
Widget A 09/30/2008 11:55:00 ON Widget B 09/30/2008 11:55:00 ON

Widget A 09/30/2008 11:57:00 OFF Widget B 09/30/2008 11:58:00 OFF


Widget A 10/01/2008 00:01:00 ON
Widget A 10/01/2008 00:02:00 OFF Widget B 10/01/2008 00:02:00 ON
Widget A 10/01/2008 00:03:00 ON

Widget A 10/01/2008 00:05:00 OFF Widget B 10/01/2008 00:05:00 OFF
Widget A 10/01/2008 00:06:00 ON Widget B 10/01/2008 00:06:00 ON




Widget A 10/01/2008 00:11:00 OFF Widget B 10/01/2008 00:11:00 OFF
Widget A 10/01/2008 00:12:00 ON

Widget A 10/01/2008 00:14:00 OFF Widget B 10/01/2008 00:14:00 ON

Widget A 10/01/2008 00:16:00 ON Widget B 10/01/2008 00:16:00 ON

-----------------------------------------------------------------------------------------------------------------------------------------------------
Phase 3
This is what I need to have at the end of the day -- but slowly getting
here, however using a few manual tasks.…

Tag Time Value Tag Time Value
Widget A 09/30/2008 11:55:00 ON Widget B 09/30/2008 11:55:00 ON
Widget A 09/30/2008 11:57:00 OFF Widget B 09/30/2008 11:58:00 OFF
Widget A 10/01/2008 00:01:00 ON Widget B 10/01/2008 00:02:00 ON
Widget A 10/01/2008 00:02:00 OFF Widget B 10/01/2008 00:05:00 OFF
Widget A 10/01/2008 00:03:00 ON Widget B 10/01/2008 00:06:00 ON
Widget A 10/01/2008 00:05:00 OFF Widget B 10/01/2008 00:11:00 OFF
Widget A 10/01/2008 00:06:00 ON Widget B 10/01/2008 00:14:00 ON
Widget A 10/01/2008 00:11:00 OFF Widget B 10/01/2008 00:16:00 OFF
Widget A 10/01/2008 00:12:00 ON
Widget A 10/01/2008 00:14:00 OFF
Widget A 10/01/2008 00:16:00 ON

 

[Joel]

You have some entries where the VALUE was blank. Not sure what to do.


Sub FixWidget()

With Sheets("Sheet1")
ColCount = 1
'loop 3 columns at a time until all data is processed
Do While .Cells(1, ColCount) <> ""
RowCount = 1
NewRowCount = 1
'loop until no more data in column
Do While .Cells(RowCount, ColCount) <> ""
Tag = .Cells(RowCount, ColCount)
Time = .Cells(RowCount, ColCount).Offset(0, 1)
Value = Cells(RowCount, ColCount).Offset(0, 2)
With Sheets("sheet2")
If RowCount = 1 Then
'Keep track of last state
State = Value
.Cells(NewRowCount, ColCount) = Tag
.Cells(NewRowCount, ColCount).Offset(0, 1) = Time
.Cells(NewRowCount, ColCount).Offset(0, 2) = Value
NewRowCount = NewRowCount + 1
Else
'if value changes then add new row
If Value <> State Then
State = Value
.Cells(NewRowCount, ColCount) = Tag
.Cells(NewRowCount, ColCount).Offset(0, 1) = Time
.Cells(NewRowCount, ColCount).Offset(0, 2) = Value
NewRowCount = NewRowCount + 1
End If
End If
End With

RowCount = RowCount + 1
Loop
ColCount = ColCount + 3
Loop
End With
End Sub

 

[ShagNasty]

Thanks for the help Joel..

The blanks were issues that my initial attempt at "programming" (using
=if(or(or(c2=c1, etc.,etc) would see as a change and the next non-blank VALUE
would be captured.

I'm sure what you gave me is a Macro; however; when I attempt to run it I
get a "Run-time error '13';" "Type mismatch". I figure it's an issue with
the TIME column which is a date & time field instead of a text field. (Ln 12,
Col1)

Also -- should their be a "." before Cells in the Value=Cells(xxx)? (Ln13,
Col1)

Again, I appreciate the help and all the magic you and your peers can do
with this stuff...
Shag...

 

[Joel]

You are right that I left out a pedriod. I also used real dates and times in
my testing. I took your posted data and ran my code with out any errors.
the format of the data and time will not matter when running this code.

I think the problem is with a header row. I did not run my tests with any
headers. So try changing this line of code if you have a header row

from
RowCount = 1

to
RowCount = 2

Also check you sheet names. My code is using Sheet1 and Sheet2. change as
required.

 

[ShagNasty]

Changing the starting roll number fixed the problem. This is a tremendous
improvement improvement over my attempt at doing the task -- thanks!!

PS -- The blank fields do cause the same issue - the blank is considered a
change in Value....

 

[ShagNasty]

Sorry to bother again...

I get 01/00/00 in all the date section of the time fields. The time section
is OK..
Ideas??

 

[Joel]

I think this ius a formating issue. try formating the summary sheet and see
what happens.

You may want to change theswe two statements

from
Time = .Cells(RowCount, ColCount).Offset(0, 1)

.Cells(NewRowCount, ColCount).Offset(0, 1) = Time

to

Time = .Cells(RowCount, ColCount).Offset(0, 1).value

.Cells(NewRowCount, ColCount).Offset(0, 1).value = Time

Also delare time

Dim Time as Date


Someplace the Time value is being truncated. time is really a DOUBLE
varianle which is 32 bits. It is probably getting converted to a long which
is 16 bits. declaring Time as Date wil solve this problem.

 

[ShagNasty]

Declaring time fixed the issue...

Thanks again..

 

[Dave Peterson]

I don't think I'd use Time as a variable name. It's way to close in spelling to
the Time keyword that VBA uses.

Dim myTime as date

???

 

[ShagNasty]

Thanks -- I considered it before and your comments just reinforced not using
"Time" as a name.

Thanks again..