Flag duplicates with multiple criteria when adding items to table

[awaeryum]

Hello everybody, I'm trying to create a spreadsheet that flags whether a new entry is a duplicate that had been already actioned previously. If it's a duplicate but it hadn't been actioned previously, then it shouldn't be flagged.
I'll try to explain this better with an example.

Important: this should be achieved without the use of VBA or macros.

Let's say that this is my starting table:

Items in columns A and B are entered by the user, who assigns to each task a status (blank if not actioned, WIP or Done).

If the user enters a duplicate task in column A that was also previously marked as Done or WIP, I would like this to be automatically flagged in column C as shown below:

If the new task is a duplicate, but wasn't previously marked as Done or WIP, then it shouldn't be flagged:

Is this something that could be done? I'm open to other ideas on how to achieve something similar or improve this procedure.

Thank you.

 

[Joe4]

You obviously don't need to check the first entry, so enter this in cell C3, and copy down:

=IF(COUNTIFS(A$2:A2,A3,B$2:B2,"Done")>0,"Yes","")

 

[Joe4]

If this is actually a table, that requires the same formula in every row, here is a slight modification that you can make to the formula to allow you to put it in cell C2 first and copy down:

=IF(AND(ROW()>2,COUNTIFS(A1:A$2,A2,B1:B$2,"Done")>0),"Yes","")

 

[awaeryum]

If this is actually a table, that requires the same formula in every row, here is a slight modification that you can make to the formula to allow you to put it in cell C2 first and copy down:

=IF(AND(ROW()>2,COUNTIFS(A1:A$2,A2,B1:B$2,"Done")>0),"Yes","")

Thank you very much, this is great!

Is there a way to include the "WIP" as well as the "Done" status in the formula?

 

[Joe4]

Thank you very much, this is great!

Is there a way to include the "WIP" as well as the "Done" status in the formula?

One way:

=IF(AND(ROW()>2,COUNTIFS(A1:A$2,A2,B1:B$2,"Done")+COUNTIFS(A1:A$2,A2,B1:B$2,"WIP")>0),"Yes","")

 

[awaeryum]

One way:

=IF(AND(ROW()>2,COUNTIFS(A1:A$2,A2,B1:B$2,"Done")+COUNTIFS(A1:A$2,A2,B1:B$2,"WIP")>0),"Yes","")

This works like a charm, thank you very much.

 

[Joe4]

You are welcome.