Count unique values within duplicate sets

In the attached spreadsheet column C contains the duplicate sets
Column C contains the values to count that are unique.
I have manually entered the row result in column P and need a formula to supply the count.
The purpose is to the delete the duplicates in column C and retain the count of unique values that existed in column P

Not sure I understand the logic for the values in column P.

Anyway, clear column P and put this in cell P1:

Formula:

Please Login or Register  to view this content.

Filter column P on values greater than 1 and delete the visible records

Trevor, thank you
I get #SPILL!
There is a heading row and 2850 rows
I believe I changed the formula correctly in the example attachment?

Did you delete the contents of column P? I did . . . please see attached.

Trevor, thank you for the fix!
However, a misunderstanding?
By unique I meant not repeated/duplicated.
The formula counts all entries in the duplicate sets
What I need is a count of unique entries in the duplicate sets
The attached spreadsheet counts 25 entries in column P for 30 Langdons Road in column M
The count should be 1 as all 25 are entries are identical not unique (in my terminology!).

All attachments have been removed because they appear to contain real email addresses and street addresses.

Please remove any private data before attaching files.

Use this instead and filter on 1 for the unique/non-duplicated values, or <>1 for the duplicates.

Oops ... in too much of a hurry.

The first formula, adjusted to reference column A:

Formula:

Please Login or Register  to view this content.

This will give a counter within each contract in column A

This is the formula that I meant to post:

Formula:

Please Login or Register  to view this content.

This will give a count of each contract

Please see the attached

You could use this, copied down:

=ROWS(UNIQUE(FILTER($M$2:$M$30,$C$2:$C$30=C2)))

Taking Rory's formula, instead of copying down, clear the results in the output column and then use:

Formula:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Originally Posted by TMS

Taking Rory's formula, instead of copying down, clear the results in the output column and then use:

Formula:

Please Login or Register  to view this content.

If you have LAMBDA and BYROW of course.

The OP's profile says 365, Rory.

That doesn't guarantee it - it depends what release channel they are on. I have 365 at work but we're on the slowest semi-annual release so I still don't have LAMBDA.

Agreed, but he also said "all resolved" in post #15, so my sleuthing skills tell me that his version is probably new enough.

This is why we prefer 365 users to say which version they have in their profiles (e.g. MS 365 v. 2308).