Search code examples
excelvbadynamic-arrays

Comparing 2 Dynamic Arrays of different sizes and seeing how many match

I have 2 Dynamic Arrays which the first Array contains 15,000 values and is located on Sheet1. The second Array has 519 Values and is located on Sheet2.

All Values in the first Array should be in the Second array, as the first array contains duplicated values.

What I want to do, is to compare the First Array with the Second Array and to return a % of how many match. (it should be 100%).

The code shown just contains the Dynamic Arrays and the start of the Formula to check both the L and U bounds of the Arrays. However I'm stuck on what the formula should say. 

Dim ws As Worksheet
Dim wb As Workbook

Dim ISRC() As Variant
Dim ISRC2() As Variant    

Set wb = Workbooks("Recordssales2019-04-05")

Dim ws1  As Worksheet
Dim ws2  As Worksheet

Set ws1 = wb.Worksheets("Recordssales2019-04-")
Set ws2 = wb.Worksheets("Metadata")

Lastrow = ws1.Range("E100000").End(xlUp).Row
ReDim ISRC(1 To Lastrow + 1)
MsgBox Lastrow

Lastrow = ws2.Range("AJ100000").End(xlUp).Row
ReDim ISRC2(1 To Lastrow + 1)
MsgBox Lastrow

For i = LBound(ISRC) To UBound(ISRC)
    If ISRC(i) = ISRC2(i) Then

Ideally for the first array to be compared to the second array and to return with a match of how many are the same. From there I want to be able to do the next step based on if they matched. If they didn't match they should not be included in the next step.

Solution

  • Try this. Populate the arrays from the ranges in one go. Loop through ISRC, check if each element is in the other array using MATCH and keep a tally.

    Sub x()

Dim ws As Worksheet
Dim wb As Workbook, lastrow As Long, i As Long, v As Variant, count As Long

Dim ISRC As Variant
Dim ISRC2 As Variant

Set wb = Workbooks("Recordssales2019-04-05")

Dim ws1  As Worksheet
Dim ws2  As Worksheet

Set ws1 = wb.Worksheets("Recordssales2019-04-")
Set ws2 = wb.Worksheets("Metadata")

lastrow = ws1.Range("E100000").End(xlUp).Row
ISRC = Application.Transpose(ws1.Range("E1:E" & lastrow).Value)
'MsgBox lastrow

lastrow = ws2.Range("AJ100000").End(xlUp).Row
ISRC2 = Application.Transpose(ws2.Range("AJ1:AJ" & lastrow).Value)
'MsgBox lastrow

For i = LBound(ISRC) To UBound(ISRC)
    v = Application.Match(ISRC(i), ISRC2, 0)
    If IsNumeric(v) Then count = count + 1
Next i

MsgBox count & " elements of ISRC are in ISRC2 (" & Format(count / UBound(ISRC), "0.0%") & ")."

End Sub