Searching for duplicates

[Nick Vittum]

(Disclaimer: You can put this in the "for educational purposes" category, although it would be extremely useful to me if I can make it work. What I don't want is to get involved in a code-heavy project that's going to slow my application down even more—but it seems that it ought to be reasonably simple.)

I scoured the web yesterday looking for ways to search for duplicate entries on a sheet. There are dozens of approaches out there, but every one I found is based on searching for all duplicates at once and in some way pointing them out on the sheet. Honestly, I thought Excel already has a built-in function for doing this....

In any case, I'm looking for something different. I'm attaching a screenshot of a rough mock-up of what I'm aiming for. Basically I'd like a userform that would (a) locate the first pair of duplicates in one column—C, in this sample; (b) display the corresponding data in other columns (A and B, here), so I can see whether the duplication is valid; and then (c) when clicked again, move on to the next pair.

Capture.JPG

Here's a typical example of the code I found out there, which I was trying to adapt to this purpose. But I can't figure out how you would interrupt the process at the first duplication, and then start it again for the next, and so on.

Code: Select all

Sub FindDups()
    Dim lastRow As Long
    Dim matchFoundIndex As Long
    Dim iCntr As Long
    lastRow = Range("C65000").End(xlUp).Row

    For iCntr = 1 To lastRow
    If Cells(iCntr, 1) <> "" Then
        matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 3), Range("C1:C" & lastRow), 0)
        If iCntr <> matchFoundIndex Then
            Cells(iCntr, 4) = "Duplicate"
       End If
    End If
    Next
End Sub

Thanks, again!

[HansV]

Code for a command button named cmdDuplicates:

Code: Select all

Private Sub cmdDuplicates_Click()
    Static r1 As Long
    Dim v As Variant
    Dim c As Range
    Dim r2 As Long
    r1 = r1 + 1
    v = Range("C" & r1).Value
    Set c = Range("C" & r1 + 1 & ":C" & Rows.Count).Find(What:=v, LookAt:=xlWhole)
    If c Is Nothing Then
        MsgBox "No more duplicates!", vbInformation
    Else
        r2 = c.Row
        Range("D" & r1).Value = "Duplicate"
        Range("D" & r2).Value = "Duplicate"
        Me.TextBox1 = Range("A" & r1).Value
        Me.TextBox2 = Range("B" & r1).Value
        Me.TextBox3 = Range("C" & r1).Value
        Me.TextBox4 = Range("A" & r2).Value
        Me.TextBox5 = Range("B" & r2).Value
        Me.TextBox6 = Range("C" & r2).Value
    End If
End Sub

TextBox1 to TextBox6 are the 6 text boxes on the userform.

[Nick Vittum]

Thank you!!! I look forward to trying this out. Please tell me, in your declarations, what does "static" mean?:

Code: Select all

    Static r1 As Long

It would seem to mean "can't be changed." But if that were so, then it seems to be contradicted by this:

Code: Select all

   r1 = r1 + 1

I'm going to try this out now

[HansV]

Static means that the variable retains its value as long as the userform is open. So each time you click the command button, it will start looking one row down.

[Nick Vittum]

I can't get this to work. I tinkered with it a lot, got nowhere (because I don't understand the code well enough) and then put it back the way you first had it. What happens

1. First click, MsgBox, "no more duplicates";
2. Next click, it prints "duplicate" in column D next the first pair of blanks (I suspect you put that there for test purposes?);
3. Next, The MsgBox again;
4. Prints next to the seconf pair of blanks;

—and so on, down to the end. I'm attaching the workbook, if you have time to look at it again? Perhaps I've forgotten something or done something wrong.

Thanks so much for your time.

[HansV]

My apologies, I hadn't included blank values in my test sheet. Here is an improved version:

Code: Select all

Private Sub cmdDuplicates_Click()
    Static r1 As Long
    Dim v As Variant
    Dim c As Range
    Dim r2 As Long
    Dim m As Long
    m = Range("C" & Rows.Count).End(xlUp).Row
    Do
        Do
            r1 = r1 + 1
            If r1 > m Then
                MsgBox "End of data", vbInformation
                Exit Sub
            End If
            v = Range("C" & r1).Value
        Loop Until v <> ""
        Set c = Range("C" & r1 + 1 & ":C" & Rows.Count).Find(What:=v, LookAt:=xlWhole)
    Loop Until Not c Is Nothing
    r2 = c.Row
    Range("D" & r1).Value = "Duplicate"
    Range("D" & r2).Value = "Duplicate"
    Me.TextBox1 = Range("A" & r1).Value
    Me.TextBox2 = Range("B" & r1).Value
    Me.TextBox3 = Range("C" & r1).Value
    Me.TextBox4 = Range("A" & r2).Value
    Me.TextBox5 = Range("B" & r2).Value
    Me.TextBox6 = Range("C" & r2).Value
End Sub

[snb]

see the attachment

[Nick Vittum]

My apologies, I hadn't included blank values in my test sheet. Here is an improved version

There's certainly no reason to apologize. I feel like I'm looking a gift horse in the mouth to say anything at all to either of you (I wonder if they have an expression like that in Holland?) But I still can't get this to work. I wish I understood the code well enough to troubleshoot it myself, but I just can't figure it out. I'm attaching my workbook again, just in case there's some difference between mine and the one you're trying this out in.

And @snb, I thank you. This version it so strange and mysterious, I feel like I'm trying to read Chinese when I look at the code. It's strange and wonderful, too, except for this: when it runs out of entries, it keeps on going until the upper row of textboxes reaches the first blank (so in other words, it's showing the entries in the upper row, and blanks in the lower row—which is no duplicate at all. Also, I tried inserting a blank row, and got an "Out of range" error. And again (even more so than with Hans's) the code is so mysterious to me that I have no clue how to troubleshoot it.

I should say to you both that I don't plan to have blank entries. I can't see any good reason to. I was just hoping to provide for that possibility to prevent failure if I should have one accidentally, or if at some point in the future I find a need for blanks that I can't foresee. But if that makes it much more complicated, I can certainly live without it!

Again, many thanks to you both

[HansV]

I feel like I'm looking a gift horse in the mouth to say anything at all to either of you (I wonder if they have an expression like that in Holland?)

The Dutch saying "Men moet een gegeven paard niet in de bek kijken" is an almost literal translation of "One shouldn't look a gift horse in the mouth".

What exactly is the problem with the workbook that you attached?
When I click the Find Duplicates button, it displays values from row 1 and row 4 (both have $20 in column C), then from row 4 and row 6 (also both $20) and finally from row 8 and row 10 (both have $90). After that, it displays "No more data".
This is what the sheet looks like at that point:

S3265.png

Can you explain what should be different in your opinion?

[snb]

As far as I know your expression is a literal translation of the Dutch one. (like Brooklyn, Bronx, Harlem, etc)

[Nick Vittum]

The Dutch saying "Men moet een gegeven paard niet in de bek kijken" is an almost literal translation of "One shouldn't look a gift horse in the mouth".

Interesting. Perhaps the expression came from the Dutch originally, then.

As far as I know your expression is a literal translation of the Dutch one. (like Brooklyn, Bronx, Harlem, etc)

I didn't know those names came from Holland. But it makes sense, since New York ("New Amsterdam") was originally a Dutch settlement

What exactly is the problem with the workbook that you attached?
When I click the Find Duplicates button, it displays values from row 1 and row 4 (both have $20 in column C), then from row 4 and row 6 (also both $20) and finally from row 8 and row 10 (both have $90). After that, it displays "No more data".

That is so strange. When I click on that same button, it immediately displays "No more data."

Now I'm even more at a loss. I'm running Excel 2016. Same for you?

[HansV]

I am using Excel 2019, but that is hardly different from Excel 2016, so it shouldn't make a difference.
Does it happen even if you close the workbook and then reopen it?

S3266.png

[Nick Vittum]

It does, indeed. The one thing I haven't tried yet is typing it in by hand, instead of pasting it. I'll try that now.

[HansV]

But the workbook that you attached higher up in this thread works for me without any modification. So there is no reason to enter the code anew...

[Nick Vittum]

Ah. That's right. Well, I did it anyway, before I saw this note. Predictably, I got the same result. I can't imagine. Unless it's some difference between 2016 and 2019, never noticed until now....

[HansV]

There is nothing in the code that is specific to Excel 2016 or 2019 (or any other version of Excel, for that matter).

Are you absolutely sure that you are using the same workbook as the one that you attached in Post=267554?

[Nick Vittum]

Yes. But just to be doubly sure, I downloaded the dupes.xlsm from here that you're using. Same result.

[HansV]

Try the following:
- Click in the line Private Sub cmdDuplicates_Click()
- Press F9 to set a breakpoint (the line will be highlighted).
- Press F5 to start the userform.
- When you click the Find Duplicates button, the code will pause at the start of cmdDuplicates_Click.
- Each time you press F8, the code will execute one line.
- You can view the value of variables such as r1 and m by hovering the mouse pointer over them.
- When you get to the line If r1 >= m Then, what is the value of r1, and what is the value of m?

[Nick Vittum]

- When you get to the line If r1 >= m Then, what is the value of r1, and what is the value of m?

Sorry to take so long. I never knew you could do this, and I've been studying the process from end to end. At

Code: Select all

 If r1 > m

r1 = 1, and m = 10. Note that there is no ">=", only ">". It seems to me that's as it should be, and what you wrote above was a typo— but I can't be sure.

Continuing through the whole cycle:

• M remains at 10 throughout, which again seems as it should be.
• r1 progresses through each row until it reaches 11, at which point the "no more data" message comes up.
• r2 remains at 0, never changes. That doesn't seem right.

[HansV]

Thanks for testing. The workbook has If r1 > m Then, but it's slightly better to test for If r1 >= m Then, so I changed it in my copy of the workbook. This doesn't make a big difference though.

Did you ever get to the line

Code: Select all

        Set c = Range("C" & r1 + 1 & ":C" & Rows.Count).Find(What:=v, LookAt:=xlWhole)

or did the code exit before that?