VBA code to replace INDEX/MATCH in large dataset

Hi there,

I have a large dataset using unique # for every customer, over 450k of them. Each of these customers also has a customer ID number.

I have a set of data where the # has been extracted but it should have been the customer ID number.

I have this code to match the customer ID to the # but due to the number or records, it slows excel a lot.

=IF(ISNA(INDEX(J:J,MATCH(C4,I:I,0))),"",INDEX(J:J,MATCH(C4,I:I,0)))

Is there a VBA solution to do exactly what that formula does, which would used less memory process please?

Thanks,

Ian

Please upload sample workbook with enough data (approx 20~50 rows) representing your actual data (of course sanitize it so you don't share any confidential info).

This sort of operation can be done via PowerQuery, MSQuery or VBA (using dictionary & Array etc).

Will do - it's VBA I'm after please.

And here is a sample sheet - have left columns as I need them for adapting any solutions in VBA - the formula in column D uses too much memory over 450k rows of data.

Based on VLOOKUP code found in link below.
http://analystcave.com/excel-vlookup...l-performance/

It also has very good article on formula, SQL lookup as well.

Please Login or Register  to view this content.

Thanks - due to the number of rows in my actual dataset, have had to amend to this;

=IF(ISNA(VLOOKUP(C2,$I:$I,1)),"",IF(VLOOKUP(C2,$I:$I,1)=C2,VLOOKUP(C2,$I:$J,2),""))

Which is coming up with nothing.

Try

=IF(ISNA(VLOOKUP(C2,$I:$I,1)),"",IF(VLOOKUP(C2,$I:$I,1,0)=C2,VLOOKUP(C2,$I:$J,2,0),""))

if unsorted

You are correct BUT (rightly or wrongly) there appears to be a consensus that INDEX/MATCH is better which is where the OP started!

CK76's VBA solution is likely to prove the winner.

Yep, on unsorted list, Index,Match or Vlookup it doesn't really matter. Performance will lag considerably on large data set (100k + Lookup).

To leap benefit of double approximate Vlookup's speed, you'd need lookup table to be sorted on key column.

Personally I prefer using PowerQuery for simplicity of managing setup (though slightly slower than VBA/MS Query/ADODB).

Hi all,
Love a lively topic
The VBA did indeed crack it as I'd suspected - was getting considerable lag on all of the other solutions, and the volume of hashtags, with differences in numbers or text being the first character, confused the sorting a bit.
Thank you CK76 - will shout if I have any problems on the full dataset but just tried a sample and it worked a treat.
Ian

Thanks for that - I shall give that a go also

Rather annoyingly - the issue is not happening on my slimmed down sheet on the previous post, as I had to get the file under 1mb to attach it.

In the original, columns D and E have 450k emails with a numerical value corresponding to it in column E.

Can wetransfer the larger file if needs be - all emails were anonymised/random generated for this version.

Can you see my images attached above which show the debug screen?

The only row that comes up on debug is this one;

Function BuildLookupCollection(categories As Range, values As Range)
Dim vlookupCol As Object, i As Long
Set vlookupCol = CreateObject("Scripting.Dictionary")
For i = 1 To categories.Rows.Count
If categories(i) <> "" Then
Call vlookupCol.Add(CStr(categories(i)), values(i))
End If
Next i

Set BuildLookupCollection = vlookupCol
End Function

What I mean is that...

Add following line in the code just before the line that produces error and see what comes up in immediate window.

Please Login or Register  to view this content.

Code uses CStr(Categories(i)) as key string. It's taken from your column D. There is duplicate value there.

Thanks - am back in office Monday so will do this then and report back. Many thanks.

Okay - I pasted the extra line, ran the macro, same error and here's a screen of what it shows on debug - did I past the extra line of code in the place you wanted me to?

error3.jpg

iantix, you need to press Ctrl + G to get the immediate window pop up. Then just copy and paste what it says onto here, that will help ascertain what the problem is.

I have started a new workbook, ro rule out issues on the older one - copied and pasted all values, then copied just the module code for the index/match. Saved separately as just one sheet in the book, 15mb filsesize.

Restarted machine.

Same exact error, same exact place - the word 'immediate' appears at the top of the box containing the 199 rows of data.

I am not 100% sure but are you sure that there isn't a duplicate ID in there? Just below the 171,417th record?

I had that thought myself and am checking now - standby

The last string that appears in immediate window. That should be duplicate.

Remove that and test.

Thanks - we're cleaning data this end as it does indeed appear that there are doubles.

Is it possible, in theory, to have the code skip or flag doubles - or even return both values associated with that email address (maybe one in one column, one in another?).

Just curious how much one could do via the VBA.

Is it possible, in theory, to have the code skip or flag doubles - or even return both values associated with that email address (maybe one in one column, one in another?).

Yes it is possible. However, you'll need to alter code a bit.

Skip is easy, just change BuildLookupCollection function as below.

Please Login or Register  to view this content.

You can add flag code before the inner Else statement.

Returning both values, is going to be an issue. You will need to use methods other than using Dictionary object, or significantly alter code logic.

Thanks - even this is a distinct improvement for me at this stage - I can filter what we know to have unique values, while a dev cleans the database by merging records.

It's actually flagged a serious issue here which is being resolved so worth the back & forth.

I shall report back on the above and eventual clean of original data. Thank YOU for your help CK76.

All I got do now is work out the flag code bit - fairly new to VBA, but asking manager for a course on it to get more familiar.