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

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

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

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

Conditional Seperation of Rows
 

OK, I see what you mean now. I'll see what I can do.
Firstly though, can you not edit SQL to get the data in the correct time
from the DB ?

NickHK

wrote in message
oups.com...
Hey Nick,

I'm sorry for the confusion.

It is in this format ...

0 = 12:00am
30 = 12:30am
100 = 1:00am
200 = 2:00am

see how there is a gap in time? i am pulling a report from a telecom
sql database and there are holes in time measured in thiry minute
intervals. there are rows of data associated with that time (the
index, if you will). what i need to do is fill that hole, or insert a
row in between the two and fill in the blank with the missing interval.
the reason for t2 - t1 = 30 is that if that is true then there is no
interval to fill. if it is false, t2 - t1 = 0 then the 30 minute
interval is missing ... make any better sense? sorry for the confusion
...


NickHK wrote:
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

Conditional Seperation of Rows
 

Assuming there are only 24 possible periods (0, 30, 100.....2300, 2330) why
not create a template table:
0 00:00
30 00:30
100 01:00
130 01:30
....etc

Then you just use VLOOKUP/MATCH or .Find and copy the input data to the
correct line in the template table.
Seems easier that checking times and inserting missing values, if the above
assumptions are correct.

NickHK

wrote in message
oups.com...
Hey Nick,

I'm sorry for the confusion.

It is in this format ...

0 = 12:00am
30 = 12:30am
100 = 1:00am
200 = 2:00am

see how there is a gap in time? i am pulling a report from a telecom
sql database and there are holes in time measured in thiry minute
intervals. there are rows of data associated with that time (the
index, if you will). what i need to do is fill that hole, or insert a
row in between the two and fill in the blank with the missing interval.
the reason for t2 - t1 = 30 is that if that is true then there is no
interval to fill. if it is false, t2 - t1 = 0 then the 30 minute
interval is missing ... make any better sense? sorry for the confusion
...


NickHK wrote:
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