I'm currently trying to optimize this macro for use with more than
30,000 rows. I thought by jumping from one ID to another it would
help but it still takes a considerable amount of time to run. Any
thoughts or tips are greatly appreicated. I have listed a sample data
piece to show how the data is currently shown.

Sub Logic_Beta()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long
Start = ""
Finish = ""
Columns("K").ClearContents
For i = 2 To Lastrow
Start = Range("F" & i)
Finish = Range("G" & i)
ID = Range("B" & i)
Z = 0
Range("B1").Activate
For j = 2 To Lastrow
If Range("B" & j) = ID Then
If j 2 Then
Cells.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
End If
If Range("F" & j) < Start And Range("F" & j) < Finish And Range("G" &
j) < Finish And Range("G" & j) Start Then
Z = Z + ((Range("G" & j) - Start) * 1440)
GoTo NextLine
End If
If Range("F" & j) Start And Range("F" & j) < Finish And Range("G" &
j) Finish And Range("G" & j) Start Then
Z = Z + ((Finish - Range("F" & j)) * 1440)
GoTo NextLine
End If
If Range("F" & j) < Start And Range("F" & j) < Finish And Range("G" &
j) Finish And Range("G" & j) Start Then
Z = Z + ((Finish - Start) * 1440)
GoTo NextLine
End If
If Range("F" & j) Start And Range("F" & j) < Finish And Range("G" &
j) < Finish And Range("G" & j) Start Then
Z = Z + ((Range("G" & j) - Range("F" & j)) * 1440)
GoTo NextLine
End If
If Range("F" & j) = Start And Range("F" & j) < Finish And Range("G" &
j) = Finish And Range("G" & j) Start Then
Z = Z + ((Finish - Start) * 1440)
GoTo NextLine
End If
NextLine:
End If
Next j
Range("K" & i) = Z
Next i
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

(In case the data isn't aligned properly.)
Columns:
A - Number
B - Who
C - RCID - Blank
D - TID - Blank
E - WID - Blank
F - Start Time
G - End Time

Number Who RCID TID WID Start Time
EndTime
A1 John 04/03/07 10:19:52 AM 04/03/07 10:22:12 AM
B2 John 04/03/07 10:26:15 AM 04/03/07 10:29:47 AM
C3 John 04/03/07 10:38:25 AM 04/03/07 10:51:37 AM
A2 John 04/03/07 10:52:20 AM 04/03/07 11:26:57 AM
B3 John 04/03/07 11:29:26 AM 04/03/07 11:38:11 AM
C4 John 04/03/07 11:55:36 AM 04/03/07 12:00:21 PM
A3 John 04/03/07 12:03:00 PM 04/03/07 12:05:28 PM
B4 John 04/03/07 12:06:22 PM 04/03/07 12:16:13 PM
C5 John 04/03/07 12:23:16 PM 04/03/07 12:35:03 PM
A4 John 04/03/07 12:50:34 PM 04/03/07 01:17:20 PM
B5 John 04/03/07 01:00:40 PM 04/03/07 01:05:59 PM
C6 John 04/03/07 01:13:57 PM 04/03/07 01:30:13 PM
A5 John 04/03/07 01:37:47 PM 04/03/07 01:43:08 PM
B6 John 04/03/07 01:47:22 PM 04/03/07 01:51:39 PM
C7 John 04/03/07 01:54:28 PM 04/03/07 02:08:48 PM

few things:

instead of
if something1 then
goto nextline
end if
if something2 then
goto nextline
end if

use
if something1 then
elseif something2 then
end if

yo have
Range("G" & j) Start
in each test, it is redundant, make just on

you have
Range("F" & j) and Range("G" & j)
calculated in each test, make them fixed at the start of the loop, lik
rj=Range("F" & j)
gj=Range("G" & j)

what is the purpose of
If j 2 Then
Cells.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

wrote in message
ps.com...
I'm currently trying to optimize this macro for use with more than
30,000 rows. I thought by jumping from one ID to another it would

Changed to the recommended here.
use
if something1 then
elseif something2 then
end if

Took this out.
yo have
Range("G" & j) Start
in each test, it is redundant, make just on

Changed this as well.
you have
Range("F" & j) and Range("G" & j)
calculated in each test, make them fixed at the start of the loop, lik
rj=Range("F" & j)
gj=Range("G" & j)

what is the purpose of
If j 2 Then
Cells.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
The intended purpose of this was to have it jump to the next row
instead of cycling down one by one. The sample data is all for one
person but the actual data may have 1,000 rows between the same ID. I
had it reset to B1 each time the row changed to ensure all the IDs
were checked starting from the beginning. Sorry if it's a bit of a
mess. Learning as I go and have learned a great from other samples
posted by everyone and trying to piece this one together myself.

je napisao u poruci interesnoj
roups.com...
Changed to the recommended here.
,
The intended purpose of this was to have it jump to the next row
instead of cycling down one by one. The sample data is all for one
person but the actual data may have 1,000 rows between the same ID. I
had it reset to B1 each time the row changed to ensure all the IDs
were checked starting from the beginning. Sorry if it's a bit of a
mess. Learning as I go and have learned a great from other samples
posted by everyone and trying to piece this one together myself.

learning is ok, nobody born learnt.

but in this case, i see no puprose, just cpu wasting, because on next prog
lines you *explicitly* address ranges with range("B" & j) and similar, so no
"activate" make any sense, just slow down.

if you want to use built in find/activate features, you need to examine
*active* cell [retrieve its range] after doing find, and not to use flat
for/next

any speed improvement so far?

sometimes it is all due to the data structure/design matter. if badly
organized data, it is hard to have lightspeed program.

keep us informed.

if you want to use built in find/activate features, you need to examine
*active* cell [retrieve its range] after doing find, and not to use flat
for/next
Not sure how this would be done or what you mean here. I did try to
just take out this find step altogether and noticed it actually ran a
little faster without so unless this new way would be a step up over
that I'll just leave it out for now.

any speed improvement so far?
A small improvement has been seen. It still takes upwards of 20
minutes for jus a couple thousand records. I considered sorting the
data to shorten the loops but doing so would eliminate the option of
allowing multiple sheets ( one per day ) as the list would always be
starting back at "A".

sometimes it is all due to the data structure/design matter. if badly
organized data, it is hard to have lightspeed program.
Would sorting it make that big of a difference? One possible solution
I see for the multiple sheets would be to carry over the ID and search
once per sheet to find the one being worked and move forward from
there.

keep us informed.
On a different note I was considering the possibility of building
arrays and working with the data there. The idea would be to grab all
the rows of a single ID and loop the arrays against each other with
the statements you saw originally. Then once the output was put on
column "K" I would a check to see if there was a value there and skip
that row if so. Thoughts on this, worth a try or way overcomplicating
it?

consider you are doing nested for/next, it means, n^2 passes.
having 10.000 rows, it is 100 milions passes. for 20 minutes, it gives
almost 100.000 pases/second, which realy isn't bad.

nested for/next loop is very unpleasant for processing, is there any chance
to serialize data [instead of n^2 to have a*n processing]?

what is your software version, cpu and mem amount?

memory augmentation may help dramaticaly

have you monitored task manager and cpu utilization. is it 100%, having
excel taking most of it?


je napisao u poruci interesnoj
oups.com...
. It still takes upwards of 20
minutes for jus a couple thousand records.

Hi Spy128Bit -

Could you briefly describe your objective with this code? What do you want
it to do? Typcially, code speaks for itself, but your code does some things
that appear redundant (but may be perfectly appropriate). I just need a bit
more information before I can decide if I can provide any help. While you
and sali work on optimizing your existing code, maybe I could work on
alternatives.

One additional question to answer specifically: It appears that your code
will produce different values in column K for each instance of the same
person. In other words, if 'Joseph' appears multiple times throughout your
database, a different Z will be calculated each time because your code gets
the variables "Start" and "Finish" from the 'current record' and then
calculates Z relative to "Start" and "Finish". Is that correct ?

Jay




" wrote:

I'm currently trying to optimize this macro for use with more than
30,000 rows. I thought by jumping from one ID to another it would
help but it still takes a considerable amount of time to run. Any
thoughts or tips are greatly appreicated. I have listed a sample data
piece to show how the data is currently shown.

Sub Logic_Beta()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long
Start = ""
Finish = ""
Columns("K").ClearContents
For i = 2 To Lastrow
Start = Range("F" & i)
Finish = Range("G" & i)
ID = Range("B" & i)
Z = 0
Range("B1").Activate
For j = 2 To Lastrow
If Range("B" & j) = ID Then
If j 2 Then
Cells.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
End If
If Range("F" & j) < Start And Range("F" & j) < Finish And Range("G" &
j) < Finish And Range("G" & j) Start Then
Z = Z + ((Range("G" & j) - Start) * 1440)
GoTo NextLine
End If
If Range("F" & j) Start And Range("F" & j) < Finish And Range("G" &
j) Finish And Range("G" & j) Start Then
Z = Z + ((Finish - Range("F" & j)) * 1440)
GoTo NextLine
End If
If Range("F" & j) < Start And Range("F" & j) < Finish And Range("G" &
j) Finish And Range("G" & j) Start Then
Z = Z + ((Finish - Start) * 1440)
GoTo NextLine
End If
If Range("F" & j) Start And Range("F" & j) < Finish And Range("G" &
j) < Finish And Range("G" & j) Start Then
Z = Z + ((Range("G" & j) - Range("F" & j)) * 1440)
GoTo NextLine
End If
If Range("F" & j) = Start And Range("F" & j) < Finish And Range("G" &
j) = Finish And Range("G" & j) Start Then
Z = Z + ((Finish - Start) * 1440)
GoTo NextLine
End If
NextLine:
End If
Next j
Range("K" & i) = Z
Next i
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

(In case the data isn't aligned properly.)
Columns:
A - Number
B - Who
C - RCID - Blank
D - TID - Blank
E - WID - Blank
F - Start Time
G - End Time

Number Who RCID TID WID Start Time
EndTime
A1 John 04/03/07 10:19:52 AM 04/03/07 10:22:12 AM
B2 John 04/03/07 10:26:15 AM 04/03/07 10:29:47 AM
C3 John 04/03/07 10:38:25 AM 04/03/07 10:51:37 AM
A2 John 04/03/07 10:52:20 AM 04/03/07 11:26:57 AM
B3 John 04/03/07 11:29:26 AM 04/03/07 11:38:11 AM
C4 John 04/03/07 11:55:36 AM 04/03/07 12:00:21 PM
A3 John 04/03/07 12:03:00 PM 04/03/07 12:05:28 PM
B4 John 04/03/07 12:06:22 PM 04/03/07 12:16:13 PM
C5 John 04/03/07 12:23:16 PM 04/03/07 12:35:03 PM
A4 John 04/03/07 12:50:34 PM 04/03/07 01:17:20 PM
B5 John 04/03/07 01:00:40 PM 04/03/07 01:05:59 PM
C6 John 04/03/07 01:13:57 PM 04/03/07 01:30:13 PM
A5 John 04/03/07 01:37:47 PM 04/03/07 01:43:08 PM
B6 John 04/03/07 01:47:22 PM 04/03/07 01:51:39 PM
C7 John 04/03/07 01:54:28 PM 04/03/07 02:08:48 PM

On Apr 19, 3:02 pm, Jay wrote:
Hi Spy128Bit -

Could you briefly describe your objective with this code? What do you want
it to do? Typcially, code speaks for itself, but your code does some things
that appear redundant (but may be perfectly appropriate). I just need a bit
more information before I can decide if I can provide any help. While you
and sali work on optimizing your existing code, maybe I could work on
alternatives.

One additional question to answer specifically: It appears that your code
will produce different values in column K for each instance of the same
person. In other words, if 'Joseph' appears multiple times throughout your
database, a different Z will be calculated each time because your code gets
the variables "Start" and "Finish" from the 'current record' and then
calculates Z relative to "Start" and "Finish". Is that correct ?

Jay

Jay,

The objective is to find the overlap in time for the same person.
Ex:
I worked 12:00 PM - 1:00PM on 4/1
I worked 12:30 PM - 1:00PM on 4/1

The value in "K" should would be 90 ( 60 for the time worked
12:00-1:00 and 30 for the overlap from the second line ).
The value in "K" on the second line would be 60 ( 30 for the time
worked 12:30-1:00PM and 30 for the overlap from the previous line ).

This is why the values in "K" are different for each line. What I
will be doing with the results is finding how productive someone is
based on their overlap.

If there's one thing I have seen on these boards it is alternative
solutions. Anything you can offer would be great. I'm glad to
receive the help I got so far and it's helped a lot. I hope that
helps explain the code a little better. If there's still questions on
something specific please let me know.

Thanks!

Hi Spy128Bit -

Thanks for the info. FYI: I'll be busy for the next 2-3 hours on some other
chores, but will take a look at your application then.
--
Jay

" wrote:

On Apr 19, 3:02 pm, Jay wrote:
Hi Spy128Bit -

Could you briefly describe your objective with this code? What do you want
it to do? Typcially, code speaks for itself, but your code does some things
that appear redundant (but may be perfectly appropriate). I just need a bit
more information before I can decide if I can provide any help. While you
and sali work on optimizing your existing code, maybe I could work on
alternatives.

One additional question to answer specifically: It appears that your code
will produce different values in column K for each instance of the same
person. In other words, if 'Joseph' appears multiple times throughout your
database, a different Z will be calculated each time because your code gets
the variables "Start" and "Finish" from the 'current record' and then
calculates Z relative to "Start" and "Finish". Is that correct ?

Jay

Jay,

The objective is to find the overlap in time for the same person.
Ex:
I worked 12:00 PM - 1:00PM on 4/1
I worked 12:30 PM - 1:00PM on 4/1

The value in "K" should would be 90 ( 60 for the time worked
12:00-1:00 and 30 for the overlap from the second line ).
The value in "K" on the second line would be 60 ( 30 for the time
worked 12:30-1:00PM and 30 for the overlap from the previous line ).

This is why the values in "K" are different for each line. What I
will be doing with the results is finding how productive someone is
based on their overlap.

If there's one thing I have seen on these boards it is alternative
solutions. Anything you can offer would be great. I'm glad to
receive the help I got so far and it's helped a lot. I hope that
helps explain the code a little better. If there's still questions on
something specific please let me know.

Thanks!

On Apr 19, 4:36 pm, Jay wrote:
Hi Spy128Bit -

Thanks for the info. FYI: I'll be busy for the next 2-3 hours on some other
chores, but will take a look at your application then.
--
Jay



" wrote:
On Apr 19, 3:02 pm, Jay wrote:
Hi Spy128Bit -

Could you briefly describe your objective with this code? What do you want
it to do? Typcially, code speaks for itself, but your code does some things
that appear redundant (but may be perfectly appropriate). I just need a bit
more information before I can decide if I can provide any help. While you
and sali work on optimizing your existing code, maybe I could work on
alternatives.

One additional question to answer specifically: It appears that your code
will produce different values in column K for each instance of the same
person. In other words, if 'Joseph' appears multiple times throughout your
database, a different Z will be calculated each time because your code gets
the variables "Start" and "Finish" from the 'current record' and then
calculates Z relative to "Start" and "Finish". Is that correct ?

Jay

Jay,

The objective is to find the overlap in time for the same person.
Ex:
I worked 12:00 PM - 1:00PM on 4/1
I worked 12:30 PM - 1:00PM on 4/1

The value in "K" should would be 90 ( 60 for the time worked
12:00-1:00 and 30 for the overlap from the second line ).
The value in "K" on the second line would be 60 ( 30 for the time
worked 12:30-1:00PM and 30 for the overlap from the previous line ).

This is why the values in "K" are different for each line. What I
will be doing with the results is finding how productive someone is
based on their overlap.

If there's one thing I have seen on these boards it is alternative
solutions. Anything you can offer would be great. I'm glad to
receive the help I got so far and it's helped a lot. I hope that
helps explain the code a little better. If there's still questions on
something specific please let me know.

Thanks!- Hide quoted text -

- Show quoted text -

I'm actually looking at the option to do a unique value filter on
columb "B" and building an array from it. The array could then be
used as a filter for the processing piece of the visible rows only.
I'm hoping that should get it down to fairly quick in processing.

Yup. Filtering came to mind. Look into the SpecialCells method (with the
xlCellTypeVisible argument) to grab the visible cells after applying the
filter.

There are programmatic techniques to accumulate unique values , too. I
think they may be a bit faster than filtering, but I don't know if they'll be
substantially faster. Won't know until we try. Gotta go for now. Very
sorry for the delay.

---
Jay




" wrote:

On Apr 19, 4:36 pm, Jay wrote:
Hi Spy128Bit -

Thanks for the info. FYI: I'll be busy for the next 2-3 hours on some other
chores, but will take a look at your application then.
--
Jay



" wrote:
On Apr 19, 3:02 pm, Jay wrote:
Hi Spy128Bit -

Could you briefly describe your objective with this code? What do you want
it to do? Typcially, code speaks for itself, but your code does some things
that appear redundant (but may be perfectly appropriate). I just need a bit
more information before I can decide if I can provide any help. While you
and sali work on optimizing your existing code, maybe I could work on
alternatives.

One additional question to answer specifically: It appears that your code
will produce different values in column K for each instance of the same
person. In other words, if 'Joseph' appears multiple times throughout your
database, a different Z will be calculated each time because your code gets
the variables "Start" and "Finish" from the 'current record' and then
calculates Z relative to "Start" and "Finish". Is that correct ?

Jay

Jay,

The objective is to find the overlap in time for the same person.
Ex:
I worked 12:00 PM - 1:00PM on 4/1
I worked 12:30 PM - 1:00PM on 4/1

The value in "K" should would be 90 ( 60 for the time worked
12:00-1:00 and 30 for the overlap from the second line ).
The value in "K" on the second line would be 60 ( 30 for the time
worked 12:30-1:00PM and 30 for the overlap from the previous line ).

This is why the values in "K" are different for each line. What I
will be doing with the results is finding how productive someone is
based on their overlap.

If there's one thing I have seen on these boards it is alternative
solutions. Anything you can offer would be great. I'm glad to
receive the help I got so far and it's helped a lot. I hope that
helps explain the code a little better. If there's still questions on
something specific please let me know.

Thanks!- Hide quoted text -

- Show quoted text -

I'm actually looking at the option to do a unique value filter on
columb "B" and building an array from it. The array could then be
used as a filter for the processing piece of the visible rows only.
I'm hoping that should get it down to fairly quick in processing.

wrote in message
oups.com...
On Apr 19, 3:02 pm, Jay wrote:
Hi Spy128Bit -

The objective is to find the overlap in time for the same person.
Ex:
I worked 12:00 PM - 1:00PM on 4/1
I worked 12:30 PM - 1:00PM on 4/1



maybe to further clarify code using function "overlap" which returns number
of minutes overlaped between two records. it is general overlap function,
calculating whatever toyou check, just keep i mind that segments must be
oriented, first start, then end point
calculate number of minutes using datediff() function, referencing some
"zero" date
--------------
Sub aaa()
m1 = DateDiff("n", reference_date, my_date1)
...
End Sub

Function overlap(p1s As Long, p1e As Long, p2s As Long, p2e As Long) As Long
If p1s < p2s Then
overlap = p1e - p2s
Else
overlap = p2e - p1s
End If
If overlap < 0 Then overlap = 0
End Function
-------------