Adjusting the Array used with Scripting Dictionary?

[nobodyukno]

Alright so I just started using arrays with scripting dictionary because I'm trying to do some Index Match formulas on workbooks that are over 300,000 rows.

So I have the following code but I get an out of memory error sometimes when running it:

Please Login or Register  to view this content.

So pretty much what the above code is saying is that my unique identifier is in column 1 of both worksheets which is what the code is looking up. I want to return column 7 from worksheet 2, and put the result in column 16 of worksheet 1.

The code works, but I get an out of memory error when running it on very large workbooks sometimes. I started using this code and my array was only the first 5 columns (x, 5), but it seems when I moved it to 7 columns (x, 7) that is when I'm getting the memory errors as each column has 300k+ rows. So I'm wondering if it's possible to adjust this array to only include my necessary columns (Columns 1 and Column 7) and leave out the other ones from the array?

Thanks.

[shg]

A formula will be faster if the unique ID column is sorted and the last argument to MATCH is 1 or omitted.

[nobodyukno]

A formula will be faster if the unique ID column is sorted and the last argument to MATCH is 1 or omitted.

I never used 1 in the index match. I need an exact match.

[nobodyukno]

I don't really understand the arrays within vba so is this code saying that the array is 7 columns wide:

Please Login or Register  to view this content.

Or is it saying its taking just columns 1 and 7? Because if it's possible to have the array just include 1 and 7 rather than the 5 additional columns, I think that will help with the memory issue.

[xlnitwit]

I never used 1 in the index match. I need an exact match.

If your data is sorted, as shg suggested, you can check for an exact match while still having the benefit of the speed of a binary search.

If you wish to use your existing code I suggest you load two arrays- one for each column- and simply iterate those.

[nobodyukno]

If your data is sorted, as shg suggested, you can check for an exact match while still having the benefit of the speed of a binary search.

If you wish to use your existing code I suggest you load two arrays- one for each column- and simply iterate those.

Does it matter if the data is sorted and not exactly the same length?

For example, as this information is monthly, there could be new data added and old data removed. So the unique ID in A54 on the old month tab might not be the same as A54 on the new month tab, it might be A66 since new data was added.

[xlnitwit]

No- as long as it is sorted, you can use a binary search. If you are returning multiple columns of data for the same lookup value, you should put the MATCH formula into a separate column as well.

The essence of the formula will be to use a binary VLOOKUP as well to ensure the matched value is the one you want
=IF(VLOOKUP(A1,lookup_table,1)=A1,match(A1,lookup_table_column_1,1),"")

[nobodyukno]

No- as long as it is sorted, you can use a binary search. If you are returning multiple columns of data for the same lookup value, you should put the MATCH formula into a separate column as well.

The essence of the formula will be to use a binary VLOOKUP as well to ensure the matched value is the one you want
=IF(VLOOKUP(A1,lookup_table,1)=A1,match(A1,lookup_table_column_1,1),"")

Does the table and lookup arrays in the formula have to be actual tables? Or can I just reference columns? Because I tried the above formula as such:

Please Login or Register  to view this content.

and then did a regular index match:

Please Login or Register  to view this content.

The index match returned about 29k #N/A's and the binary formula returned about 15k blanks and 700 or so #N/A's.

[xlnitwit]

No, they do not have to be tables. You should only need to sort the Final sheet by column A.

[nobodyukno]

No, they do not have to be tables. You should only need to sort the Final sheet by column A.

And one more caveat is that column A are numbers stored as text so I guess I'm going to have to convert to numbers?

[xlnitwit]

You shouldn't have to. You just need to ensure that whatever you look up is the same data type as the lookup column.

[jindon]

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and see if you still have error or not.

[nobodyukno]

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and see if you still have error or not.

Works perfectly.

Thank you!