This is sample data to check duplicate complaint. we have trace duplicated complaint with “Product Serial No.” and if complaint is “Open” then our formula will show that “Complaint is duplicate” other wise the formula will show blank, check out the video below and download the sample file from download button as given here under.
Formula applied in Column i =IF(COUNTIF($F$2:$F$27,F4)>1,”Duplicate”,””)
Formula applied in Column J =IF(AND(H4=”Open”,I4=”Duplicate”),”Repeated Complaint”,””)
Formula in Conditional Formatting =$J2=”Repeated Complaint”
Download Practice File (Excel Format)