PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Capturing data only when a column "Value" changes

Reply
Thread Tools Rate Thread

Capturing data only when a column "Value" changes

 
 
ShagNasty
Guest
Posts: n/a
 
      24th Oct 2008
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
 
Reply With Quote
 
 
 
 
Joel
Guest
Posts: n/a
 
      24th Oct 2008
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 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
>
>
 
Reply With Quote
 
ShagNasty
Guest
Posts: n/a
 
      24th Oct 2008
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 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
> >
> >
 
Reply With Quote
 
Joel
Guest
Posts: n/a
 
      24th Oct 2008
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" wrote:

> 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 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
> > >
> > >
 
Reply With Quote
 
ShagNasty
Guest
Posts: n/a
 
      25th Oct 2008
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....


"Joel" wrote:

> 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" wrote:
>
> > 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 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
> > > >
> > > >
 
Reply With Quote
 
ShagNasty
Guest
Posts: n/a
 
      25th Oct 2008
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" wrote:

> 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" wrote:
>
> > 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 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
> > > >
> > > >
 
Reply With Quote
 
Joel
Guest
Posts: n/a
 
      25th Oct 2008
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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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 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
> > > > >
> > > > >
 
Reply With Quote
 
ShagNasty
Guest
Posts: n/a
 
      25th Oct 2008
Declaring time fixed the issue...

Thanks again..

"Joel" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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 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
> > > > > >
> > > > > >
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      25th Oct 2008
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

???

Joel wrote:
>
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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 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
> > > > > >
> > > > > >

--

Dave Peterson
 
Reply With Quote
 
ShagNasty
Guest
Posts: n/a
 
      26th Oct 2008
Thanks -- I considered it before and your comments just reinforced not using
"Time" as a name.

Thanks again..

"Dave Peterson" wrote:

> 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
>
> ???
>
> Joel wrote:
> >
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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 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
> > > > > > >
> > > > > > >
>
> --
>
> Dave Peterson
>
 
Reply With Quote
 
 
 
Reply

« Deleting blank cells w/o impacting other rows/columns | install graphical filters »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Pivot Table Data with Column "Sum" rather than "count" defa Johnny_99 Microsoft Excel Misc 2 2nd Jan 2010 03:25 PM
Any way to make "E-mail Display As" column populate with the "E-mail"column data? Jay Mueller Microsoft Outlook Contacts 4 19th Sep 2008 04:51 AM
find "Cancellation" in column "A" and copy all data from Columns B-F onto another Sheet bjohnson Microsoft Excel Programming 1 20th Sep 2007 04:02 PM
Change data in a single column from "last, first" to "first last" =?Utf-8?B?SmVhbm5l?= Microsoft Excel Misc 3 27th Mar 2006 08:40 PM
Capturing "validating" event in TextBox of form negates normal "esc key" action Steve Roggow Microsoft Dot NET Framework Forms 2 9th Dec 2003 04:39 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 10:06 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top