| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capturing data only when a column "Value" changes
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capturing data only when a column "Value" changes
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
| find "Cancellation" in column "A" and copy all data from Columns B-F onto another Sheet | Excel Programming | |||
| Change data in a single column from "last, first" to "first last" | Excel Discussion (Misc queries) | |||
| Please add a "sheet" function like "row" and "column" functions | Excel Programming | |||
| Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming | |||
Linear Mode
