VBA Duplicate Finding based on date

[jr0124]

I'm creating a workbook to manage my leads as they come through with some details that'll then trigger to go to a working quote sheet. What I am looking to do is note the duplicate requests that we receive. The way I am hoping I can get it to work is below.

• Check column C and column E to see if both are duplicates
• If so, check column A to see if the date is exact
• If so, in column I type in "Duplicate Request"

Then, I'd like to do another check

• Check column C and column E to see if both are duplicates
• If so, check column A to see if the date is within 5 days
• If so, in column I change the fill of the cell to red

Any ideas?

 

[Saurabhj]

Hi ,
In the below statement are we checking whether date is of today ?

• If so, check column A to see if the date is exact

Thanks,
Saurabh

 

[jr0124]

Hi ,
In the below statement are we checking whether date is of today ?

• If so, check column A to see if the date is exact

Thanks,
Saurabh

I dont think it'll be today in all cases so let's go with no. More if it matches the other row exactly

 

[Saurabhj]

Hi,

Check if below code works.

Sheet1 is worksheet name.

Sub checkDuplicate()
    Dim lastRowColC As Integer, rowno As Integer
    
    lastRowColC = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    For rowno = 1 To lastRowColC
     With Sheets("Sheet1")
        If .Range("C" & rowno) = .Range("E" & rowno) Then
            If Int(.Range("A" & rowno)) = Date Then
                .Range("I" & rowno) = "Duplicate Request"
            ElseIf Date - Int(.Range("A" & rowno)) <= 5 Then
                .Range("I" & rowno).Interior.Color = vbRed
            End If
    
        End If
   End With
Next

 

[6StringJazzer]

This does not require VBA, and in fact I would discourage it for this problem.

I assume that date is "within 5 days" means the one date is ±5 days of the other date.

Use this formula in column I:

=IF(COUNTIFS($C:$C,$C2,$E:$E,$E2,$A:$A,$A2)>1,"Duplicate Request","")

Use this conditional formatting rule for column I:

=COUNTIFS($C:$C,$C1,$E:$E,$E1,$A:$A,">="&$A1-5,$A:$A,"<="&$A1+5)>1

jr0124=sample.xlsx
	A	B	C	D	E	F	G	H	I
1	Some date		Some data		Some other data				Flags
2	5/26/2021		Unique C 1		Unique E 1
3	5/26/2021		Unique C 2		Unique E 2
4	5/26/2021		Unique C 3		Unique E 3
5	5/26/2021		Exact Dupe C		Exact Dupe E				Duplicate Request
6	5/26/2021		Unique C 5		Unique E 5
7	5/26/2021		Unique C 6		Unique E 6
8	5/26/2021		Close In Date C		Close In Date E
9	5/26/2021		Unique C 8		Unique E 8
10	5/26/2021		Unique C 9		Unique E 9
11	5/26/2021		Exact Dupe C		Exact Dupe E				Duplicate Request
12	5/26/2021		Unique C 11		Unique E 11
13	5/28/2021		Close In Date C		Close In Date E
14	5/21/2021		Close In Date C		Close In Date E
Sheet1

Cell Formulas
Range	Formula
C2:C4,C12,C9:C10,C6:C7	C2	="Unique C "&ROW()-1
E2:E4,E12,E9:E10,E6:E7	E2	="Unique E "&ROW()-1
I2:I12	I2	=IF(COUNTIFS($C:$C,$C2,$E:$E,$E2,$A:$A,$A2)>1,"Duplicate Request","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Expression	=COUNTIFS($C:$C,$C1,$E:$E,$E1,$A:$A,">="&$A1-5,$A:$A,"<="&$A1+5)>1	text	NO