Simple Formula or VBA for Verifying Duplicates + one Criteria

[bradmsg]

Hi all,

I have been racking my brain for a couple weeks and need some help on this. Sometimes I over look over simple answer and I need someone to put me out of my misery.

Problem:

I have a 300,000 record long list. There are multiple duplicate numbers in column A and a date in column B. I need to check if ALL duplicates are within a certain date range (Between D2 and D4).

 

[frabulator]

Question, what is the condition if it is found to be true? IE, what do you want to have done with the duplicates within that date range? List them? Move them? Delete them?

--EDIT--
Never mind, I think I see now. I completely skipped over the section in F.

So it looks like you just want to list the numbers that are present? Do you want to list how many duplicates of each? What are you looking for exactly?

 

[bradmsg]

Question, what is the condition if it is found to be true? IE, what do you want to have done with the duplicates within that date range? List them? Move them? Delete them?

--EDIT--
Never mind, I think I see now. I completely skipped over the section in F.

So it looks like you just want to list the numbers that are present? Do you want to list how many duplicates of each? What are you looking for exactly?

Thank you for your Reply!

I would like it to list each number (from column A) once in column F IF: all duplicates from column A are within the start end date.

For the example screen shot from above, it only list 2 and 3 because they are the only 2 that meet both criteria.

I hope this makes sense. Please let me know if I can clarify further!

 

[bradmsg]

Thank you for your Reply!

I would like it to list each number (from column A) once in column F IF: all duplicates or singe values from column A are within the start end date.

For the example screen shot from above, it only list 2 and 3 because they are the only 2 that meet both criteria.

I hope this makes sense. Please let me know if I can clarify further!

Im sorry, to clarify : --> IF: all duplicates or singe values from column A are within the start end date.

I suppose to check if its a duplicate first, if so, are all duplicates with in the range--> if that is true, return one instance of that number to column F.

 

[frabulator]

I am not very good at formulas, but I do know a bit about vba.

I have used vba to make the following macro and assigned it to a button using the same layout as your page. Please see the image.

The code clears the cells in F3:F1000, loops through the dates to compare, and then list the dupes under F3 only if they currently do not exist.

Sub LookForDupes()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ws.Range("F3:F1000").Value = ""         'clears cells
    
    Dim SD As Date
    Dim ED As Date
    Dim TD As Date
    
    SD = ws.Cells(2, 4).Value
    ED = ws.Cells(4, 4).Value
    
    Dim V As String
    
    For i = 2 To 1000
        V = ""
        TD = ws.Cells(i, 2).Value
        If TD >= SD And TD <= ED Then       'its a match, so check
            V = ws.Cells(i, 1).Value
            Dim F As Boolean
            F = False
            For x = 3 To 1000               'checks to see if it is there already
                If ws.Cells(x, 6).Value = V Then
                    F = True
                    Exit For
                End If
            Next
            
            If F = False Then
                For x = 3 To 1000
                    If ws.Cells(x, 6).Value = "" Then
                        ws.Cells(x, 6).Value = V
                        Exit For
                    End If
                Next
            End If
            
        End If
            
    Next
    

End Sub

 

[bradmsg]

Thank you very much for this! I will try it out on my data.

It was probably my explanation but, I need it to check if all duplicates are within the range. if you look at column a, "1" has one date that is in the range and one that is out. It should not pull this number because not all duplicates are within the range. Is this possible in VBA?

I will follow through with the code, i just wanted to get a quick reply back to you while you are still on. Thank you again for your help!!!!

 

[frabulator]

Thank you very much for this! I will try it out on my data.

It was probably my explanation but, I need it to check if all duplicates are within the range. if you look at column a, "1" has one date that is in the range and one that is out. It should not pull this number because not all duplicates are within the range. Is this possible in VBA?

I will follow through with the code, i just wanted to get a quick reply back to you while you are still on. Thank you again for your help!!!!

so, it needs to have all variables (as in, all 1's) within the date range?
That that is so, then you just made this much more difficult.

 

[bradmsg]

so, it needs to have all variables (as in, all 1's) within the date range?
That that is so, then you just made this much more difficult.

That is correct. Any ideas?

 

[frabulator]

Try this. It adds in an extra check to see if there are duplicates that are outside the date range. If there are, then it will not include them.

Sub LookForDupes()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ws.Range("F3:F1000").Value = ""         'clears cells
    
    Dim SD As Date
    Dim ED As Date
    Dim TD As Date
    
    SD = ws.Cells(2, 4).Value
    ED = ws.Cells(4, 4).Value
    
    Dim V As String
    
    For i = 2 To 1000
        V = ""
        TD = ws.Cells(i, 2).Value
        If TD >= SD And TD <= ED Then       'its a match, so check
            V = ws.Cells(i, 1).Value
            
            'now we need to loop and see if there are any more
            Dim Alone As Boolean
            Alone = False
            For q = 2 To 1000
                If q <> i Then
                    If ws.Cells(q, 1).Value = V Then
                        TD = ws.Cells(q, 2).Value
                        If TD >= SD And TD <= ED Then
                        Else
                            Alone = True
                            Exit For
                        End If
                    End If
                End If
            Next
            
            If Alone = False Then
                Dim F As Boolean
                F = False
                For x = 3 To 1000               'checks to see if it is there already
                    If ws.Cells(x, 6).Value = V Then
                        F = True
                        Exit For
                    End If
                Next
                
                If F = False Then
                    For x = 3 To 1000
                        If ws.Cells(x, 6).Value = "" Then
                            ws.Cells(x, 6).Value = V
                            Exit For
                        End If
                    Next
                End If
            End If
        End If
    Next
    
End Sub

Also, welcome to the form!

If you find an answer on here dont forget to mark the post as the answer so others will know the question has been resolved.

 

[bradmsg]

YOU ARE A ****ING GENIOUS!!!!!!!!

That was exactly what I need it to do!!!!!!! I cant thank you enough! I have been banging my head against the wall for weeks trying to get this functionality! I owe you a beer! or 10!