Capturing data only when a column "Value" changes
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" wrote:
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" wrote:
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 aint got the
faintest cue what Im 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
|