Formula to look for duplicates using multiple criteria

dds5300

Hello,

I currently have this formula which is looking for duplicates in APPID:

=IF(COUNTIF(APPID:APPID, APPID@row) > 1, "FLAG")

I would like to add another formula to this which will look first at the APPID to see if they are the same, and then look in the N column to see if the name is the same.

If the name is the same, then it should return "FLAG", if the name is not the same, it should not flag it all.

APPID = 1234, and N = ABC

APPID = 1234, and N = DEF

In this case, the formula should not flag this as a duplicate because the value of N is not the same. If the value of N had been ABC in the second occurrence, then FLAG should be returned.

I was wondering if someone could please help me figure this one out, thanks!

Paul Newcome

Change from a COUNTIF to a COUNTIFS (with the "S" on the end) to use multiple range/criteria sets and then add your new range/criteria set to the COUNTIFS.

dds5300

Thank you, what criteria would I use to ensure that column N is not the same on the two rows. For example, column N cannot Car and Car, and have the same APP ID. I want the formula to return FLAG when the APPID is the same AND the N is not both Car and Car.

Paul Newcome

It would be the same logic as you already have. You are counting rows where the APPID is the same as "@row", you would do the same with N "@row".