Identify matches between sheet columns

batman1 · #1 02-02-2025, 04:03 PM

In OVERLAY are the results of what you want to do? What should I do to get such results? Run the BuildOverLay macro right away? I'm asking because I haven't analyzed the code yet. I'll just run XLSM and what should I do next to get the results in OVERLAY?

batman1 · #2 02-02-2025, 04:11 PM

I think you first need to display the Form. And what next, what and where do you need to enter, what to select, what to click to finally click "CREATE OVERLAY" and get what I see in the OVERLAY? You have to tell me to save me time analyzing the code: what to select in each ComboBox, what to enter into the TextBox, which OptionButton to select before clicking the CommandButton "CREATE OVERLAY".

gmaxey · #3 02-02-2025, 04:28 PM

1. Open the file I attached. Delete the current Overlay sheet.
2. On the QAT, click the button to Initiate Overlay
3. When the userform opens click create family relationships
4. Click Create Overlay

A new overlay sheet is created with the relationships in columns 11 12 and 13.

batman1 · #4 02-03-2025, 03:12 AM

Is this a code snippet?

Code:

With oOS
    lngOSLastRow = oOS.Range("A" & oOS.Rows.count).End(xlUp).Row
    If oFrm.optCreateFam Then
      oBasicBar.StatusMessage = "Defining Family relationship ... Please Wait"
'      co lngRecordIndex dong du lieu trong OVERLAY
      lngOSRow = 2
       Do While Len(.Cells(lngOSRow, 1).value) > 0
        'See min3 function notes below for more information
        .Cells(lngOSRow, lngOS_FamColNum) = min3(.Range(Cells(2, lngOS_FamMatchColIndex), Cells(lngOSLastRow, lngOS_FamMatchColIndex)), .Cells(lngOSRow, lngOS_FamMatchColIndex).value, .Range(Cells(2, lngOS_NDSRIColNum), Cells(lngOSLastRow, lngOS_NDSRIColNum)), .Range(Cells(2, 1), Cells(lngOSLastRow, 1)))
        If .Cells(lngOSRow, lngOS_FamColNum) <> .Cells(lngOSRow, 1) Then
          If WorksheetFunction.CountIfs(.Range(Cells(2, lngOS_FamMatchColIndex), Cells(lngOSLastRow, lngOS_FamMatchColIndex)), .Cells(lngOSRow, lngOS_FamMatchColIndex).value, .Range(Cells(2, 1), Cells(lngOSLastRow, 1)), .Cells(lngOSRow, lngOS_ParColNum)) = 0 Then
            .Cells(lngOSRow, lngOS_ParColNum) = .Cells(lngOSRow, lngOS_FamColNum)
          End If
        End If
        lngOSRow = lngOSRow + 1
        If lngOSRow Mod 100 = 0 Then oBasicBar.StepBar
      Loop
    End If

Do you want to write the above fragment and function min3 differently?
It is now 11:00 (UTC+01:00). I will check in the evening

gmaxey · #5 02-03-2025, 05:18 AM

Batman,

I am not sure exactly what you mean by "snippet," but as that part of the code is only run if the "Create Family Relationships" is checked; I believe it would be considered a snippet.

It is currently run after the varOverlay_Data array is written to the Overlay sheet, because the min3 functions uses the Worksheet function CountIf and that functions requires the data to be in the sheet.

I have attached another example file with the progress bar removed, fewer REF_Data rows and some comments in the code.

With the sample file, the code will run very fast. I assume you will readily see, that if this "Create Family Relationships" is used on an Overlay sheet with thousands or tens of thousands of records, it would again take a long time to loop through all of the data in the Overlay sheet records.

It there were some way to achieve this "snippet' result while still working with the array, then that problem might be resolved. Do you think adding the overlay array record values to a dictionary as they were created would give us the opportunity to achieve an equivalent "CountIf" in the snippet and min3 process with the Overlay_data array before writing to the Overlay sheet? If so, do you think doing so would take less time.

I am not asking to be given the fish. I am making some progress on determining the answers to those questions myself. Give me some time. If I get stuck, I reach out. If not, I will post the solution I find. Thanks for all of your interest and help.

batman1 · #6 02-03-2025, 10:41 AM

1. I tested on Overlay Tool Sample Data BM Ver 1.1.xlsm (Not this file: Overlay Tool Fam Rel Sample Data BM.xlsm) - 11:19 02/02/2025
2. Test
- Firstly click the button to InitiateOverlay --> OVERLAY
- Make the following changes and try again click the button to InitiateOverlay --> OVELAY (1)
- test code

Code:

Sub test()
Dim arr_old(), arr_new(), i As Long, j As Long
arr_old = Worksheets("OVERLAY").Range("A2:L837").value
arr_new = Worksheets("OVERLAY(1)").Range("A2:L837").value
For i = 1 To 836
    For j = 1 To 12
        If arr_old(i, j) <> arr_new(i, j) Then
            Debug.Print i, j
        End If
    Next j
Next i
MsgBox "he he he"
End Sub

both sheets are identical
-------------------------

All changes:

1. In "Sub BuildOverLay" add

Code:

Dim count As Long, i As Long, k As Long

2. Before this fragment

Code:

If Not oFrm.optCreateFam Then
    If lngRecordIndex Then oOS.Range("A2").Resize(lngRecordIndex, lngCols + 1).value = varOverlay_Data
 Else
    If lngRecordIndex Then oOS.Range("A2").Resize(lngRecordIndex, lngCols + 4).value = varOverlay_Data
End If

Paste this fragment

Code:

If oFrm.optCreateFam Then
    oBasicBar.StatusMessage = "Defining Family relationship ... Please Wait"
    For i = 1 To UBound(varOverlay_Data, 1)
        varOverlay_Data(i, lngOS_FamColNum) = min3(varOverlay_Data, lngOS_FamMatchColIndex, varOverlay_Data(i, lngOS_FamMatchColIndex), lngOS_NDSRIColNum)
        If varOverlay_Data(i, lngOS_FamColNum) <> varOverlay_Data(i, 1) Then
'        -----------------------
'           instead of CountIfs - start
            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
'            ---------------------
            If count = 0 Then
                varOverlay_Data(i, lngOS_ParColNum) = varOverlay_Data(i, lngOS_FamColNum)
            End If
        End If
    Next i
End If

3. New version of function min3

Code:

Private Function min3(varOverlay_Data, ByVal lngOS_FamMatchColIndex As Long, ByVal strFamily As String, ByVal lngOS_NDSRIColNum As Long) As String
Dim lngIndex As Integer, rM As Integer
Dim bFirstMatch As Boolean, count As Long, i As Long
    bFirstMatch = True
    rM = varOverlay_Data(1, lngOS_NDSRIColNum)                    '   205
    min3 = varOverlay_Data(1, 1)                          '    ''#2.1
    For lngIndex = 1 To UBound(varOverlay_Data, 1)
        If varOverlay_Data(lngIndex, lngOS_FamMatchColIndex) = strFamily Then       ' 38
            If bFirstMatch Then
                rM = varOverlay_Data(lngIndex, lngOS_NDSRIColNum)   ' 205
                min3 = varOverlay_Data(lngIndex, 1)     '   #2.1
                bFirstMatch = False
            Else
                If rM > varOverlay_Data(lngIndex, lngOS_NDSRIColNum) Then
                    rM = varOverlay_Data(lngIndex, lngOS_NDSRIColNum)     '   204/203
                    min3 = varOverlay_Data(lngIndex, 1)   '   #98.1/#665.1
                End If
            End If
        End If
    Next
'    ------------------------
'    instead of CountIfs - start
    count = 0
    For i = 1 To UBound(varOverlay_Data, 1)
        If varOverlay_Data(i, lngOS_NDSRIColNum) = rM Then count = count + 1
    Next i
'    instead of CountIfs - end
'    ------------------------
    If count > 1 Then
        min3 = varOverlay_Data(1, 1)
        bFirstMatch = True
        For lngIndex = 1 To UBound(varOverlay_Data, 1)
            If varOverlay_Data(lngIndex, lngOS_NDSRIColNum) = rM Then
                If bFirstMatch Then
                    min3 = varOverlay_Data(lngIndex, 1)
                    bFirstMatch = False
                Else
                    If stripNonNums(min3) > stripNonNums(varOverlay_Data(lngIndex, 1)) Then
                        min3 = varOverlay_Data(lngIndex, 1)
                    End If
                End If
            End If
        Next
    End If
lbl_Exit:
  Exit Function

End Function

4. Change to (I added Byval)

Code:

Private Function stripNonNums(ByVal strText As String)

Tomorrow I will write a version of CountIfs using Dictionary

batman1 · #7 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

gmaxey · #8 02-03-2025, 12:51 PM

Batman,
I have not yet looked through your last two posts, because as I mentioned in my last post, I have been trying to sort this out myself. I am beginning to think that you enjoy a good Excel challenge as much as I enjoy a good Word challenge.

I am posting what I have put together. I have some meetings this afternoon, but will look over your code later tonight or tomorrow. If you want to just post the file with your changes, I can step through your code that way too. Up to you.

Thank you for all of your help!!!

batman1 · #9 02-03-2025, 01:04 PM

Quote:

Originally Posted by gmaxey

If you want to just post the file with your changes, I can step through your code that way too. Up to you.

I gave you detailed instructions. That's enough.

gmaxey · #10 02-03-2025, 05:42 PM

Quote:

Originally Posted by batman1

I gave you detailed instructions. That's enough.

Batman,
Moving passed my initial annoyance with your last, I followed your steps and modified the attached version 1.1 per your instructions. Yes, it works very well and very fast.

In the attached version 1.2 (posted earlier), I used three dictionaries. It also works fast and very well and, mentioning this only for others reading this, a little faster than version 1.1. I believe this is due to there is no looping to replicate the CountIf and CountIfs functions.

Ver 1.1 or 1.2 would not have been possible for me without your help. Thank you again.

P.S. Ver 1.2 is modified to disable the options for Duplicate Row and Copy Control ID when Create Family Relationship is selected.

gmaxey · #11 02-03-2025, 04:34 PM

Batman,

You don't have to be snippy. But, since you were, I didn't ask you for those instructions. I specifically stated that I didn't want to be given the fish, but be given a chance to learn to fish for myself based on the excellent suggestions and help you have previously provided. Thanks to you, I have something that is working fine (much better than I had before starting this exchange with you). It is attached to my last post.

I used dictionaries to get around the CountIf and CountIfs worksheet functions. You obviously have a file that contains your revised code. If you don't want to attached it for me to review and compare that is fine. I just don't see any point in trying revise an earlier version of my code with your changes.

The thread is solved. Thank you again for all of your time, interest, patience and attention.

batman1 · #12 02-04-2025, 12:37 AM

Quote:

Originally Posted by gmaxey

Batman,

You don't have to be snippy. But, since you were, I didn't ask you for those instructions.

It's a pity that you took my answer that way. I'll explain why I gave instructions and not a finished file. If someone gives me instructions on how and where to make changes in the code, I can correct the code myself and I'm 100% sure that I only have changes in those places. If someone gives me a corrected file, despite their assurances, I'm not 100% sure that I only have changes there. Maybe that someone changed something else in other places and simply forgot. I have important code and I want to be aware of every change. That's why I'll have to check every line of very long code. I'll waste a lot of time. For the same reasons, I gave instructions and not a corrected file. I put a lot of time into making changes and regretted a few seconds to attach a finished file? Since you rated me so low, there's nothing to discuss. Good luck.

gmaxey · #13 02-04-2025, 04:59 AM

Holy Cow Batman! Rated you so low? Where did that come from? I think if you look back through this thread you will see that I have showered you with expressions of appreciation and praise for your assistance, interest, time and skill.

I fully understand why you initially gave instructions instead of a completed file. However, as I had already achieved (again thanks to you) and posted a solution, I no longer needed your instructions. I told you that and simply suggested (not asked) if you wanted to send your file for me to compare with mine that was up to you. You could have replied sure, your explanation, or not at all. Instead you were snippy. That wasn't' the first time. If you look back through the thread you will see several instances when you scolded me for not providing details, and then instances where I did and you reply: Just Yes or No, you don't have to explain.

I see that you are new to this forum. You bring a lot to plate and I sense that you enjoy finding solutions to Excel problems and you enjoy sharing your knowledge with others. I've been in the sister Word forum for a dozen years or more. There are a number of times where I have posted there something like "we are not a code writing service ... we are here to help you learn to write your own code" when I felt like someone was just asking for a solution on a plate. Perhaps that is what you thought about me. In any case I didn't just want a fish. I wanted to learn to catch my own fish and thanks to you, I did.

Now, if there is somewhere in this thread where I "rated" you low, I am not aware of it. If you will point it out, I will try to correct it.

batman1 · #14 02-04-2025, 05:17 AM

Quote:

Originally Posted by gmaxey

Holy Cow Batman!

For my answer "I gave you detailed instructions. That's enough" you thought I was snippy? And all I mean is that I gave you detailed instructions, and if you wanted to you could handle it. That I don't see any reason to attach a file to it. That's all. I don't want to discuss.

gmaxey · #15 02-04-2025, 07:01 AM

Taken in consideration with a few previous statements, yes. You asked, I answered. No one is making you reply.

Considering my ... "I am not asking to be given the fish. I am making some progress on determining the answers to those questions myself. Give me some time. If I get stuck, I'll reach out. If not, I will post the solution I find. Thanks for all of your interest and help." ... way back in post #36, couldn't one assume the discussion could have ended there?

Keep up the good work. You still have not indicated where I gave you a "low" rating. To avoid any confusion. On a scale of 1 to 10

Knowledge 10
Helpfulness 10

People Skills 6

The discussion can end or not. Up to you.

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