Efficiency issue when looping through each cell

**221080** · 07-30-2010, 07:30 AM

I have a workbook with weekly data for a whole year (100.000+ records per week) that I need to compare to another workbook with 100.000 records and look for overlapping periods.
Each record in the weekly sheet needs to be marked as checked when the comparison has been performed and if there is an overlapping period that needs to be marked as well and the record copied to a whole other sheet.

I have done this by looping through each week checking every new record against the 100.000 records (via find), but this is quite time consuming, so I am wondering if this can’t be done more efficiently. Maybe by using matrixes?

Any help is greatly appreciated.

Please Login or Register  to view this content.

**royUK** · 07-30-2010, 08:17 AM

Would it be possible to filter the data using AutoFilter?

**221080** · 07-30-2010, 10:32 AM

Originally Posted by royUK

Would it be possible to filter the data using AutoFilter?

Possibly, but I do not see an immediate gain from doing that. What are you thinking?

**shg** · 07-30-2010, 12:19 PM

I think someone would need to see a reasonable amount of (sanitized) data to make a useful suggestion.

Throughout your code, you do this:

Please Login or Register  to view this content.

The With statement isn't doing anything. References should be like this:

Please Login or Register  to view this content.

**221080** · 07-30-2010, 12:30 PM

Originally Posted by shg

I think someone would need to see a reasonable amount of (sanitized) data to make a useful suggestion.

I will attach sample data as soon as I can, but it is just a list of numbers on the weekly sheets, being compared to another list with numbers and a start and finish date.

Throughout your code, you do this:

Please Login or Register  to view this content.

The With statement isn't doing anything. References should be like this:

Please Login or Register  to view this content.

It is indeed sloppy coding.
The sheet is activated though so the code still determines the ranges, so I do not see this having any real impact on the efficiency.

**royUK** · 07-30-2010, 01:21 PM

AutoFilter would be much faster than a Loop

**221080** · 07-30-2010, 07:02 PM

Originally Posted by shg

I think someone would need to see a reasonable amount of (sanitized) data to make a useful suggestion.

I have a small example of how the data in the different sheets look.

Example of the data on one of the weekly sheets
Example of data on the control sheet that each of the records on the weekly sheet is checked against.

**221080** · 07-31-2010, 11:33 AM

Originally Posted by royUK

AutoFilter would be much faster than a Loop

Could you elaborate a little on how that could be done in this example?

**shg** · 07-31-2010, 11:36 AM

The dates in your example don't make much sense.

Please Login or Register  to view this content.

**221080** · 07-31-2010, 11:40 AM

It is the version format. mm/dd/yyyy
12/31/9999 indicates that there is no end date, i.e it is still running as of today.

**shg** · 07-31-2010, 11:42 AM

What should be the output for the given example, and why?

**221080** · 07-31-2010, 11:50 AM

If the record is on the control sheet and the dates overlap, i.e. the start date is before the monday of the week of the weekly sheet and the end date is after that monday, the record on the weekly sheet is marked as included.

Please Login or Register  to view this content.

and the record is then copied to a third sheet.

Please Login or Register  to view this content.

To check all the records and move/mark the included ones are the purpose of this piece of code.

**221080** · 08-01-2010, 11:50 AM

It was suggested that the fastest way was to use arrays, which I have implemented below.

I had to loop through the registry-array as I could not find a (reasonable) way to search it and return the element number.

So I think what I have now works and it is also a lot faster. However, it is now looping through a 100,000 elements for the registry for every record every week (100,000 records a week), which might not be the most efficient...

Please Login or Register  to view this content.

Efficiency issue when looping through each cell

LinkBack

Thread Tools

Rate This Thread

Display

Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Re: Efficiency issue when looping through each cell

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions