count unique records in one column based on criteria in a different column

Maybe:

Sub rxg2669()
Dim i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
Sheets.Add.Name = "Name Count"
ws.Activate
For i = Range("A" & Rows.Count).End(3).Row To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i + 1).Value Then
    Sheets("Name Count").Range("A" & Rows.Count).End(3)(2) = Range("A" & i).Value
    Rows(i + 1).Insert
    End If
Next i
Range("B2").Select
zz:
If ActiveCell.Offset(2) = "" Then
ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(3).Row).SpecialCells(4).EntireRow.Delete
Exit Sub
End If
        x = 0
        Do Until ActiveCell.Value = ""
            If Cells(ActiveCell.Row, "B") <> Cells(ActiveCell.Row + 1, "B") Then x = x + 1
            ActiveCell.Offset(1).Select
        Loop
        Sheets("Name Count").Range("B" & Rows.Count).End(3)(2) = x
        If ActiveCell.Value = "" And ActiveCell.Offset(1) <> "" Then ActiveCell.Offset(1).Select
        GoTo zz
  
End Sub

Hi Davis,

Is this logic will work for 40 Sheets. as per my below example. please Suggest and thanks for your help!

For Example if I have two sheets, I should not manually enter Sheet 1 and Sheet 2 in the Code it is to be automated in the Code. Can you please help me on this.

I need to Create Final sheet as in the Third Table.

In Final Sheet I need to remove the duplicate and show in First Column and Number of FAIL in second Column and Number of PASS in Third Column.

Please Note that the logic should work automatically even if the Client add a New sheet or delete the Existing Sheet. Please help me on this.

Shee1

Table Name	Test Case	Test Result
ABC	ABC_Test Case 1	PASS
ABC	ABC_Test Case 2	FAIL
ABC	ABC_Test Case 3	FAIL
XYZ	XYZ_Test Case 1	FAIL
XYZ	XYZ_Test Case 2	FAIL
CDE	CDE_Test Case 1	PASS
CDE	CDE_Test Case 2	PASS

Sheet 2

Table Name	Test Case	Test Result
CCC	CCC_Test Case 1	PASS
CCC	CCC_Test Case 2	FAIL
DDD	DDD_Test Case 1	FAIL
DDD	DDD_Test Case 2	PASS
DDD	DDD_Test Case 3	FAIL
DDD	DDD_Test Case 4	FAIL

Final Sheet Should be created as below

Table Name	COUNT OF FAILS	COUNT OF PASS
ABC	2	1
XYZ	2	0
CDE	0	2
CCC	1	1
DDD	3	1