Search code examples
excelexcel-formulaexcel-2010excel-2007

Identify similar entries based on two criteria

I'm trying identify different status of a product for same colour by using the below formula

=IF(B6&J6=B5&J5,IF(OR(B6&J6&L6=B5&J5&L5,B6&J6&L6=B7&J7&L7),"True","False"),"True")

Result

enter image description here

Expected Result

enter image description here

However, unable to achieve the expected result. I completely lost here kindly lend me a hand :)

Solution

  • Here is a possible answer. Count how many times the most common status (Seasonal) occurs for the current product and colour, then count how many times the status in the current row occurs. If the counts are the same, then this is the most common status so set the result to TRUE, otherwise FALSE.

    =COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,C2)=MAX(COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,$C$2:$C$20))

    Must be entered as an array formula using CtrlShiftEnter

    or you can try

    =COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,C2)=AGGREGATE(14,0,COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2,$C$2:$C$20,$C$2:$C$20),1)

    entered as a normal formula.

    Here is a screen shot using your specified columns

    enter image description here