IF function for unique/last occurring unique cases?

Hi there,

I have an excel file that lists individuals who came into the clinic on a given day. I am interested in figuring out the amount of unique individuals in a given month/overall. As this is a working datasheet, I would like a formula that would automatically update this information for me. Here is an example of the file that I am working with: unique case example.xlsx

Specifically, I want an IF function that could place an 'x' in column E depending on if the name in column C is a unique instance. But if the name appears several times in the list, I would also like this function to place an 'x' in column E next to the last occurring unique instance. For example, the name 'Hanna D' occurs 3 times in the spreadsheet I have attached, but I want the 'x' to be next to the most recent incident, when she was screened, as opposed to when she was not contacted (missed/doc forgot).

That being said, I am not sure if you can even define a unique case in the IF function, let alone the last occurring unique case, but any suggestions would be very helpful.

Thank you!

-D

PS I am not interested in conditional formatting (highlighting duplicate cases and then placing an 'x' next to the most recent highlighted case) because my spreadsheet has 300+ individuals and it is very time consuming to manually move the 'x' when the same individual appears at a later time.

Put in E3 and copied down:

=IF(AND(A3=INDEX(A:A,MAX(($C$3:$C$36=C3)*ROW($C$3:$C$36))),COUNTIF($C$3:$C$36,C3)>1),"X","")

dont forget to press CTRL-SHIFT-ENTER button together, coz this Array Formula

Azumi

Great! That solves my problem with finding the last occurring unique case. But can I also apply a formula that does the same thing to a unique case (that does not repeat)? And would it be possible to put both of these formulas in the same column so that I have an 'x' appear next to all unique cases AND last occurring unique cases if there are doubles?

Thanks for taking the time to respond!

Perfect thank you so much!