Code Snippets

Option Explicit 

Dim gvaPositionsPrevious As Variant 
Dim gvaPositionsCurrent As Variant 

Public Enum enARRAY_ENUM 
    eKey 
    eName 
    eDescription 
    ePrevious 
    eCurrent 
End Enum 

Public Sub Arrays_Processing() 

Dim lcount_previous As Long 
Dim lcount_current As Long 
Dim blast_previous As Boolean 
Dim blast_current As Boolean 
Dim lrow_output As Long 
Dim boutput_previous As Boolean 
Dim boutput_current As Boolean 
Dim boutput_both As Boolean 

    On Error GoTo ErrorHandler 

    gvaPositionsPrevious = Range("PositionsPrevious").Value 
    gvaPositionsCurrent = Range("PositionsCurrent").Value 
    lrow_output = 3 
    lcount_previous = 1 
    lcount_current = 1 
    Sheets("Previous vs Current").Select 
    Range("B3:I5000").ClearContents 
    With Sheets("Previous vs Current") 

        Do Until ((blast_previous = True) And (blast_current = True)) 

            boutput_both = False 
            boutput_current = False 
            boutput_previous = False 
            If (blast_previous = True) Then boutput_current = True 
            If (blast_current = True) Then boutput_previous = True 
            If ((blast_previous = False) And _ 
                (blast_current = False)) Then 
                If (gvaPositionsPrevious(lcount_previous, enARRAY_ENUM.eKey) = _ 
                    gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eKey)) Then 
                    boutput_both = True 
                Else 
                    If (gvaPositionsPrevious(lcount_previous, enARRAY_ENUM.eKey) < _ 
                        gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eKey)) Then 
                        boutput_previous = True 
                    Else 
                        'therefore Previous > Current, so display Current
                        boutput_current = True 
                    End If 
                End If 
            End If 

            If (boutput_both = True) Then 
                .Range("B" & lrow_output).Value = _ 
                      gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eName) 
                .Range("C" & lrow_output).Value = _ 
                      gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eDescription) 
                'display previous
                .Range("D" & lrow_output).Value = _ 
                      gvaPositionsPrevious(lcount_current, enARRAY_ENUM.ePrevious) 
                'display current
                .Range("E" & lrow_output).Value = _ 
                      gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eCurrent) 
                If (lcount_previous = UBound(gvaPositionsPrevious, 1)) Then 
                   blast_previous = True 
                End If 
                If (lcount_previous < UBound(gvaPositionsPrevious, 1)) Then 
                   lcount_previous = lcount_previous + 1 
                End If 
                If (lcount_current = UBound(gvaPositionsCurrent, 1)) Then 
                   blast_current = True 
                End If      
                If (lcount_current < UBound(gvaPositionsCurrent, 1)) Then 
                   lcount_current = lcount_current + 1 
                End If 
            End If 

            If (boutput_previous = True) Then 
                .Range("B" & lrow_output).Value = _ 
                    gvaPositionsPrevious(lcount_current, enARRAY_ENUM.eName) 
                .Range("C" & lrow_output).Value = _ 
                    gvaPositionsPrevious(lcount_current, enARRAY_ENUM.eDescription) 
                'display previous
                .Range("D" & lrow_output).Value = _ 
                    gvaPositionsPrevious(lcount_current, enARRAY_ENUM.ePrevious) 
                If (lcount_previous = UBound(gvaPositionsPrevious, 1)) Then 
                    blast_previous = True 
                End If 
                If (lcount_previous < UBound(gvaPositionsPrevious, 1)) Then 
                    lcount_previous = lcount_previous + 1 
                End If 
            End If 

            If (boutput_current = True) Then 
                .Range("B" & lrow_output).Value = _ 
                   gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eName) 
                .Range("C" & lrow_output).Value = _ 
                   gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eDescription) 
                'display current
                .Range("E" & lrow_output).Value = _ 
                   gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eCurrent) 
                If (lcount_current = UBound(gvaPositionsCurrent, 1)) Then 
                   blast_current = True 
                End If 
                If (lcount_current < UBound(gvaPositionsCurrent, 1)) Then 
                   lcount_current = lcount_current + 1 
                End If 
            End If 

            'Difference between current and previous
            .Range("F" & lrow_output).FormulaR1C1 = "RC[-1]-RC[-2]" 
            lrow_output = lrow_output + 1 
        Loop 
    End With 
    Range("B3").Sort Key1:=Range("C3"), Order1:=xlAscending, _ 
                     Key2:=Range("C4"), Order2:=xlAscending, Header:=xlYes 
    Exit Sub 

ErrorHandler: 
End Sub 

Public Sub MatchingArrays() 
'Dim vArray1 As Variant
'Dim vArray2 As Variant
Dim larray1_count As Long 
Dim larray2_count As Long 
Dim barray1_last As Boolean 
Dim barray2_last As Boolean 

Dim lcount_combined As Long 

    ReDim vCombinedList(1 To UBound(vArray1, 1) + UBound(vArray2, 1)) 

    larray1_count = 1 
    larray2_count = 1 
    lcount_combined = 1 
    
    Do Until (barray1_last = True) And (barray2_last = True) 
    
        If vArray1(larray1_count) = vArray2(larray2_count) Then 
        
            vCombinedList(lcount_combined) = vArray1(larray1_count) 
            lcount_combined = lcount_combined + 1 
        

            If larray1_count < UBound(vArray1, 1) Then larray1_count = larray1_count + 1 
            If larray2_count < UBound(vArray2, 1) Then larray2_count = larray2_count + 1 
            
            If larray1_count = UBound(vArray1, 1) Then barray1_last = True 
            If larray2_count = UBound(vArray2, 1) Then barray2_last = True 
        Else 
            If vArray1(larray1_count) < vArray2(larray2_count) Then 
        
                If larray1_count = UBound(vArray1, 1) Then barray1_last = True 
                If larray1_count < UBound(vArray1, 1) Then larray1_count = larray1_count + 1 
            Else 
            
                If larray2_count = UBound(vArray2, 1) Then barray2_last = True 
                If larray2_count < UBound(vArray2, 1) Then larray2_count = larray2_count + 1 
            End If 
        End If 
    Loop 
    
    ReDim Preserve vCombinedList(1 To lcount_combined - 1) 
    
    Stop 

End Sub