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

Hello

I'm trying to create a macro that will insert another tab that lists each name in column a and then counts the unique records in column b for each name. I've been searching the forums and can't find a solution but that could be because I'm not wording my search correctly. I attached a sample worksheet. The solution should be:

Jane R Doe - 8
Susie Q brown - 7
Bob M Jones - 6
Tony F Harris - 5

Any help would be greatly appreciated.

Thanks,
Ronna

I don't think a pivot table would work. I posted a very simple worksheet. My actual worksheet will have 20 additional columns of information that will get broken out into seperate tabs based on the names. I need to make sure I have a certain number of records for each person before I start manipulating the data.

John Davis,
That's it, that is exactly what I was looking for. Thank you very much!

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

It looks like what your spreadsheet is doing is counting the number of records in column b that are the same. What I'm looking for is something that will tell me that Jane Doe has 8 unique records in column b, Susie Brown has 7 unique records in column b, etc.
Thanks for taking the time to look at this.

Did you check the results on the added sheet? Does it reflect what you show in Post #1 as the required result. If your data is sorted as in your example it should give your a unique count.

You're welcome. Glad to help out and thanks for the feedback.

Thank you Sooooooooooo.... much!

It Solved my problem.