Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Seperation of Rows
I have a particularly frustrating problem ... I have a column of values like:
0 30 100 200 230 330 These are time stamps that correspond as follows 0 = 12:00am, 1 = 12:30am, etc . There are times missing in any given report (it's a sql pull) and i need to fill in the blanks. There are whole rows of data that are associated with these time stamps. What I need to do is compare every two rows to see if there are inconsistencies ... essentially it follows a difference of 30 between every row. In pseudo code I see it like this: for loop (from first cell to last cell) excel.range rngTop excel.range rngBottom does rngBottom - rngTop = 30? if yes then move to the next cell down if no then select the entire row and all subsequent rows to the end then move the selection to one row below it move down the column repeat same action Any ideas? I'm using VSTO 2005 and C# as my preferred language. If you are more comfortable with VB then that's fine, I'll attempt to translate. It is really coming down to my lack of knowledge of the object model. Thank you in advance for any help you can give. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Seperation of Rows
Paul,
So (T2-T1)/30 should give you the number of 30 minute period between the 2 times ? But then some of your values are not valid; 100, 200, 230 Or am I missing something ? NickHK "Paul Shoemaker" wrote in message ... I have a particularly frustrating problem ... I have a column of values like: 0 30 100 200 230 330 These are time stamps that correspond as follows 0 = 12:00am, 1 = 12:30am, etc . There are times missing in any given report (it's a sql pull) and i need to fill in the blanks. There are whole rows of data that are associated with these time stamps. What I need to do is compare every two rows to see if there are inconsistencies ... essentially it follows a difference of 30 between every row. In pseudo code I see it like this: for loop (from first cell to last cell) excel.range rngTop excel.range rngBottom does rngBottom - rngTop = 30? if yes then move to the next cell down if no then select the entire row and all subsequent rows to the end then move the selection to one row below it move down the column repeat same action Any ideas? I'm using VSTO 2005 and C# as my preferred language. If you are more comfortable with VB then that's fine, I'll attempt to translate. It is really coming down to my lack of knowledge of the object model. Thank you in advance for any help you can give. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Seperation of Rows
Well, I was basing my conditional statement on the fact that if T2-T1 =
30 then there is a valid progression ie 100, 130, 200, etc ... so in your scenario the same conditional statement would yield 1. My main boggle is selecting a row ... cutting or copying that row and then subsequently moving that to another location ... I was trying it with some combination of //there is a for loop in there for a known number of rows and this is the body of my else statement rngOff = rng.get_range("A" + i + ":L" + i, missing); rngOff.cut(rng.get_offset(0, 1)); BUT! this is doing it correctly for the first row but not for each subsequent row b/c it doesn't move the entire "section" down ... if i used a range say A1:L36 in get_range it still makes no difference ... does that make any more sense? NickHK wrote: Paul, So (T2-T1)/30 should give you the number of 30 minute period between the 2 times ? But then some of your values are not valid; 100, 200, 230 Or am I missing something ? NickHK "Paul Shoemaker" wrote in message ... I have a particularly frustrating problem ... I have a column of values like: 0 30 100 200 230 330 These are time stamps that correspond as follows 0 = 12:00am, 1 = 12:30am, etc . There are times missing in any given report (it's a sql pull) and i need to fill in the blanks. There are whole rows of data that are associated with these time stamps. What I need to do is compare every two rows to see if there are inconsistencies ... essentially it follows a difference of 30 between every row. In pseudo code I see it like this: for loop (from first cell to last cell) excel.range rngTop excel.range rngBottom does rngBottom - rngTop = 30? if yes then move to the next cell down if no then select the entire row and all subsequent rows to the end then move the selection to one row below it move down the column repeat same action Any ideas? I'm using VSTO 2005 and C# as my preferred language. If you are more comfortable with VB then that's fine, I'll attempt to translate. It is really coming down to my lack of knowledge of the object model. Thank you in advance for any help you can give. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Seperation of Rows
You've still lost me...
T1: 0 = 0 = 12:00 T2: 30 = 1 =12:30 T3: 100 T4: 200 T5: 230 T6: 330 = 11 = 17:30 So what is the meaning of say T3-T2=70 ? NickHK wrote in message oups.com... Well, I was basing my conditional statement on the fact that if T2-T1 = 30 then there is a valid progression ie 100, 130, 200, etc ... so in your scenario the same conditional statement would yield 1. My main boggle is selecting a row ... cutting or copying that row and then subsequently moving that to another location ... I was trying it with some combination of //there is a for loop in there for a known number of rows and this is the body of my else statement rngOff = rng.get_range("A" + i + ":L" + i, missing); rngOff.cut(rng.get_offset(0, 1)); BUT! this is doing it correctly for the first row but not for each subsequent row b/c it doesn't move the entire "section" down ... if i used a range say A1:L36 in get_range it still makes no difference ... does that make any more sense? NickHK wrote: Paul, So (T2-T1)/30 should give you the number of 30 minute period between the 2 times ? But then some of your values are not valid; 100, 200, 230 Or am I missing something ? NickHK "Paul Shoemaker" wrote in message ... I have a particularly frustrating problem ... I have a column of values like: 0 30 100 200 230 330 These are time stamps that correspond as follows 0 = 12:00am, 1 = 12:30am, etc . There are times missing in any given report (it's a sql pull) and i need to fill in the blanks. There are whole rows of data that are associated with these time stamps. What I need to do is compare every two rows to see if there are inconsistencies ... essentially it follows a difference of 30 between every row. In pseudo code I see it like this: for loop (from first cell to last cell) excel.range rngTop excel.range rngBottom does rngBottom - rngTop = 30? if yes then move to the next cell down if no then select the entire row and all subsequent rows to the end then move the selection to one row below it move down the column repeat same action Any ideas? I'm using VSTO 2005 and C# as my preferred language. If you are more comfortable with VB then that's fine, I'll attempt to translate. It is really coming down to my lack of knowledge of the object model. Thank you in advance for any help you can give. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sales data seperation | Excel Worksheet Functions | |||
time seperation | Excel Worksheet Functions | |||
Text String Seperation. | Excel Worksheet Functions | |||
Seperation of prime and sub inventory units | Excel Worksheet Functions | |||
Conditional Sum Rows | Excel Worksheet Functions |