Select duplicates in 1 column based on criterias in other

[crazym12]

Hello everyone,

seeking anyones help with following task. I am searching for duplicated values in 1 column based on preset conditions in a different column.
Example as below
Capture.PNG

From picture I need to search for duplicates in first column based on conditions in the last columns (for example in this case MS to be always prefered and flag the SW as duplicate)
Any help would be very appreciated.

Thank you,
Martin.

[63falcondude]

Hello and welcome to the forum.

Attach a sample workbook (not just a screenshot).

Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[crazym12]

Hello Falcondude,

Thank you, I have attached a simplified example document.

Regards,
Martin

[63falcondude]

Based on the sample workbook that you shared in post #1, I see that condition "C" has precedence (i.e. will not get marked as "Yes" unless all are "C") over any other conditions.

My only questions here are:
1) Why should row 6 be marked as "Yes" instead of row 7?
2) Why should row 9 be marked as "Yes" instead of row 8?

[63falcondude]

Try this in C2:

=IF(OR(COUNTIFS(A$2:A2,A2,B$2:B2,"C")>1,COUNTIFS(A$2:A2,A2,B$2:B2,"<>C")>1,AND(COUNTIFS(A:A,A2,B:B,"C")>0,B2<>"C")),"Yes","")

[leelnich]

Or this ARRAY FORMULA* in C2, copied down:

=IF(ROWS(A$2:A2)=MATCH(1,(A$2:A$9=A2)*IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"C"),B$2:B$9="C",1),0),"","Yes")

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.