Case- and character sensitive match and count with VBA

[lopiteaux]

Hi,

I need to compare 2 datasets, both with ~20k records, and determine whether a record in dataset A is present in dataset B.

The keys I need to compare need to take into account:
- case sensitivity (the keys are only unique when this distinction is made);
- wildcards (the keys can contain *, ?, ~, &, $, <, etc.)


I can accomplish the task using an exact match lookup (below), but the calculation time is ~10-15mins. Is there a better and faster way to do this via VBA?

{=INDEX(data_column,MATCH(TRUE,EXACT(value,lookup_column))}

Cheers,
l.

 

[Fluff]

Could you show us a sample of your data,, indicating what column/s to look in?
Also what do you want to happen if a match is found?

 

[lopiteaux]

A sample value is "@$E109*201|RTS22" - unfortunately I cannot share a sample as the data is highly confidential.

I need to return the row number of the MATCH, in the same way that a traditional MATCH works. I'm having to expand the MATCH to include the EXACT function given the special characters and case sensitivity.

 

[Rick Rothstein]

I need to compare 2 datasets, both with ~20k records, and determine whether a record in dataset A is present in dataset B.

Where are your two data sets located (sheet name or names, columns starting row)?

Where should the output go (next column)?

 

[lopiteaux]

Where are your two data sets located (sheet name or names, columns starting row)?

Where should the output go (next column)?

The structure is as follows:
- Lookup values in D2:D20001 on sheet 'baseline';
- MATCH range in C2:C20001 on sheet 'report';
- INDEX range in E2:E20001 on sheet 'report';
- Response from INDEX/MATCH in E2:E20001 on sheet 'baseline';

Apologies for being vague!

 

[Fluff]

Is this what your after

Sub CompareCopy()

   Dim Valu As String
   Dim Cl As Range
   Dim BaseWs As Worksheet
   Dim RepWs As Worksheet
   
   Set BaseWs = Sheets("Test")
   Set RepWs = Sheets("SBD")
   With CreateObject("scripting.dictionary")
      For Each Cl In RepWs.Range("C2", RepWs.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2).Value
      Next Cl
      For Each Cl In BaseWs.Range("D2", BaseWs.Range("D" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With

End Sub

 

[lopiteaux]

Thanks Fluff, that's exactly what I needed. You've brought it down to just over a second!

 

[Fluff]

Glad we could help & thanks for the feedback