Compare & Fill Data - Large Data Sets - Question

Hi,
i have been struggling with a problem for a while now and i hope some of you guys may be able to help me.

Basically I am trying to compare data in two columns and in case of a match fill a third column of data. Everything seems to be easy but the one problem is that the columns i compare contain hundreds of thousands of data values (rows). Meaning that in the on example i am working on i have to do a 700000x800000 rows matching, which will take probably days (i never reached the end, i break up my macro after a couple of hours)

I've saved a copy of my data on the following address:

https://app.box.com/s/cp3zd7sgexjcxzjhy3lp


In the example provided i'm trying to do the following:
Compare values from sheet States column L with Sheet1 column A and if there is a Match, check the Index in States sheet (column K) and put 1 of the corresponding column in Sheet1 and 0 to all other columns in sheet 1 (J:S)

3 Macros are included in the file which all do the job but for this data size are practically not applicable.

My question is, is it possible to modify the code in a way that a reasonable run time is achieved? Its clear to me that it wont be finished in minutes but a couple of hours will be great.


Thank you in advance.

Hi Mitko,
can there be multiple matches in column L (States) and column A (Sheet1)?

I haven't looked at your file (I have a bit of a phobia about downloading executables from the web) but I can tell you that when working with large data sets, it is much quicker to read the data into arrays and compare them in memory vs reading from cells in a spreadsheet. Just dimension a couple arrays as variant, then set the arrays equal to the ranges you need to examine. That should work much faster.

No there shouldn't be any multiple matches, however due to some measurements errors it could happen that there some (which can be ignored)

Originally Posted by gjcase

.. it is much quicker to read the data into arrays and compare them in memory vs reading from cells in a spreadsheet.

Hi,
thanks for the effort. Your suggestion is exactly the way i am proceeding at the moment. However it still requires a enormous amount of time.


Best Regards

Mitko,
unexpected difficulties arose. I copied some of your data to a new file (attached). There are discrepancies between the data that must meet. For example, the macro ttt() says F6 on Sheet1 is not equal to the L6 on States.
Perhaps this is due to regional settings. You ttt() macro gives the same values ​​as me?

Nilem,
i assume this is a result of converting the Date to Unix format. Perhaps there is a decimal point somewhere i the data on the States sheet. I had some similar problems and i solved it by converting the values to strings, or, if you know a way to cut the decimal part. The first couple of decimal digits are 0 but i am quite sure somewhere there is a value and that makes the difference.

Okay, change the formula in column L on a sheet of States as follows:

Formula:

Please Login or Register  to view this content.

The macro is almost ready, I just will check at runtime. Do not go away

Thanks mate, just make sure you check it on the large data set i provided, because that's where trouble came before

Here's a file with two macros. ertert() advantageous to use, if there are a large number of matches. If a small number of matches, it is best to apply ertert22(). Both macros are checked on your data, both run about 40 seconds.
Just copy the modules with these macros in your file.
Pre I changed the formula in your original file and copied piece of data in my file.

Please Login or Register  to view this content.

Hi Mitko
you can use this modified code

Please Login or Register  to view this content.