Identify matches between sheet columns

batman1 · #31 02-03-2025, 11:25 AM

However, there is no point in using Dictionary, because with the improved FOR the code is faster than with Dictionary

1. We will see this fragment in BuildOverLay

Code:

count = 0
            For k = 1 To UBound(varOverlay_Data, 1)
                If varOverlay_Data(k, lngOS_FamMatchColIndex) = varOverlay_Data(i, lngOS_FamMatchColIndex) Then
                    If varOverlay_Data(k, 1) = varOverlay_Data(i, lngOS_ParColNum) Then
                        count = count + 1
                    End If
                End If
            Next k
'           instead of CountIfs - end
'            -------------------

We'll soon have a condition check: If count = 0 Then. So with count = 1 we already know that the condition is not met, so we don't have to finish the FOR loop. Instead of the above fragment, this fragment will suffice

Code:

count = 0
For k = 1 To UBound(varOverlay_Data, 1)
                If varOverlay_Data(k, lngOS_FamMatchColIndex) = varOverlay_Data(i, lngOS_FamMatchColIndex) Then
                    If varOverlay_Data(k, 1) = varOverlay_Data(i, lngOS_ParColNum) Then
                        count = 1
                        Exit For
                    End If
                End If
            Next k

2. Similarly change the fragment in min3

Code:

count = 0
    For i = 1 To UBound(varOverlay_Data, 1)
        If varOverlay_Data(i, lngOS_NDSRIColNum) = rM Then count = count + 1
    Next i

na ten fragment

Code:

count = 0
    For i = 1 To UBound(varOverlay_Data, 1)
        If varOverlay_Data(i, lngOS_NDSRIColNum) = rM Then
            count = count + 1
            If count > 1 Then Exit For
        End If
    Next i

Now the code is faster

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to Remove the subtotal columns in a sheet	Marcia	Excel	3	12-01-2023 05:48 AM
Relating cells in a row in one sheet to cells in columns on another sheet.	mbesspiata3	Excel	2	01-06-2017 05:42 AM
Create a New Sheet from Existing Sheet with Specific Columns	malam	Excel Programming	1	10-17-2014 10:01 PM
From an XL sheet ,how to keep the group of columns which match with other XL sheet	Zubairkhan	Excel	2	03-04-2014 10:57 PM
Removing columns within sheet	shabbaranks	Excel	2	09-11-2012 05:03 AM