Duplicate Rows

[jsmucker24]

I have a table with Employee time data. For an accuracy check, I need a formula that counts the number of the duplicate employee names with duplicate dates (if any) See the sample table attached)
If I have any duplicate rows then I know that there is a problem because an employee can't work twice in one day.
I was unsuccessful with Chat GPT. Any help would be appreciated. Thank you!

 

[Logit]

Here is a VBA answer :

Sub CheckDuplicates()
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust the sheet name if necessary
    
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Clear previous highlights
    ws.Range("A1:B" & lastRow).Interior.ColorIndex = 0
    
    ' Loop through each row and compare values in Column A and Column B
    For i = 2 To lastRow ' Start at row 2 to skip header
        For j = i + 1 To lastRow
            If ws.Cells(i, 1).Value = ws.Cells(j, 1).Value And _
               ws.Cells(i, 2).Value = ws.Cells(j, 2).Value Then
                ' Highlight duplicates
                ws.Cells(i, 1).Interior.Color = RGB(255, 0, 0) ' Red highlight for duplicate
                ws.Cells(i, 2).Interior.Color = RGB(255, 0, 0) ' Red highlight for duplicate
                ws.Cells(j, 1).Interior.Color = RGB(255, 0, 0) ' Red highlight for duplicate
                ws.Cells(j, 2).Interior.Color = RGB(255, 0, 0) ' Red highlight for duplicate
            End If
        Next j
    Next i
    
    MsgBox "Duplicate check complete!"
End Sub

And here are two formula answers :

 

[kvsrinivasamurthy]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Date
2	aaaa	16-01-2025						aaaa	16/1/2025, 22/1/2025
3	aaaa	22-01-2025						nnn	12-01-2025
4	aaaa	16-01-2025
5	nnn	12-01-2025
6	aaaa	22-01-2025
7	nnn	12-01-2025
8	nnn	25-01-2025
9
Sheet1

Only dates are taken and Timings are not taken.
For 365 version in H2

=LET(da,A2:A8,db,B2:B8,a,COUNTIFS(da,da,db,db),za,UNIQUE(FILTER(da,a>1)),zb,BYROW(za,LAMBDA(x,TEXTJOIN(", ",1,UNIQUE(FILTER(TEXT(db,"mm/dd/yyyy"),(da=x)*(a>1)))))),HSTACK(za,zb))

My default date format is dd/mm/yyyy

 

[kvsrinivasamurthy]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Date
2	aaaa	16-01-2025						Names	Duplicate Dates
3	aaaa	22-01-2025						aaaa	16/1/2025, 22/1/2025
4	aaaa	16-01-2025						nnn	12-01-2025
5	nnn	12-01-2025
6	aaaa	22-01-2025
7	nnn	12-01-2025
8	nnn	25-01-2025
Sheet1

Including the heading formula in H2

=LET(da,A2:A8,db,B2:B8,a,COUNTIFS(da,da,db,db),za,UNIQUE(FILTER(da,a>1)),zb,BYROW(za,LAMBDA(x,TEXTJOIN(", ",1,UNIQUE(FILTER(TEXT(db,"mm/dd/yyyy"),(da=x)*(a>1)))))),hdr,HSTACK("Names","Duplicate Dates"),VSTACK(hdr,HSTACK(za,zb)))

 

[kvsrinivasamurthy]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Date	Start Time	End Time							Names	Duplicate Dates
2	aaaa	16-01-2025	2:00 AM	6:00 PM							aaaa	16-01-2025
3	aaaa	22-01-2025	4:00 AM	2:00 PM							nnn	12-01-2025
4	aaaa	16-01-2025	5:00 PM	10:00 PM
5	nnn	12-01-2025	3:00 PM	10:00 PM
6	aaaa	22-01-2025	3:00 PM	7:00 PM
7	nnn	12-01-2025	9:00 PM	11:00 PM
8	nnn	25-01-2025	10:00 AM	6:00 PM
Sheet2

Considering both Date and Timings.
In K1

=LET(da,A2:A8,db,B2:B8,dc,C2:C8,dd,D2:D8,a,MAP(da,db,dc,dd,LAMBDA(p,q,r,s,COUNTIFS(da,p,db,q,dc,"<="&s,dd,">="&r))),nm,UNIQUE(IF(a>1,da,"")),dt,UNIQUE(IF(a>1,db,"")),za,UNIQUE(FILTER(nm,nm<>"")),zb,BYROW(za,LAMBDA(Ro,TEXTJOIN(", ",1,IF(nm=Ro,TEXT(dt,"mm/dd/yyyy"),"")))),hdr,HSTACK("Names","Duplicate Dates"),VSTACK(hdr,HSTACK(za,zb)))

 

[Peter_SSs]

an employee can't work twice in one day

Would it be any use then to just use a relatively simple Conditional Formatting formula to highlight the problem rows?

25 02 22.xlsm
	A	B
1	Employee	Date
2	John	20/02/2025
3	Sarah	20/02/2025
4	John	20/02/2025
5	Mark	21/02/2025
6	John	21/02/2025
7
8
9
10
Duplicates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B10	Expression	=COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2)>1	text	NO