Duplicate Check Formula

Rahul Bhandari

Hello There,

I have created a column for duplicate check, It will flag the as red if both conditions are true

Condition 1 : CPR ID Duplicate

Condition 2 : Amount Duplicate

Formula which is giving me error:

=IF(AND(COUNTIF([CPR ID#]:[CPR ID#], [CPR ID#]1) > 1, IF(COUNTIF(Amount:Amount, Amount1) > 1, 1)))

I think i am not using AND function properly

@Paul Newcome @L@123

Please help, Thanks

Paul Newcome

Try this...

Get the Count first:

=COUNTIFS([CPR ID#]:[CPR ID#], [CPR ID#]1, Amount:Amount, Amount1)

Then say that if the count is greater than 1, flag. There is no need for the AND statement in this one.

=IF(COUNTIFS(............) > 1, 1)

=IF(COUNTIFS([CPR ID#]:[CPR ID#], [CPR ID#]1, Amount:Amount, Amount1) > 1, 1)

L_123

@Paul Newcome Is correct. That is how I would do it.

Rahul Bhandari

Thank you so much for the help @Paul Newcome & @L@123 . I just need to remove the cells where CPR ID# & Amount Column is either N/A or Blank

I tried with below but its not taking the later conditions - am i again missing AND? and if yes can you also show me how we can incorporate AND ( if it requires*)

=IF(COUNTIFS([CPR ID#]:[CPR ID#], [CPR ID#]@row, Amount:Amount, Amount@row) > 1, 1, IF([CPR ID#]@row, ="", 0, IF([CPR ID#]@row, ="N/A", 0, IF(Amount@row, ="", 0, IF(Amount@row, ="N/A"), 0))))

Its showing duplicates for below rows but its not the case since its either N/A or Blank :

Paul Newcome

Nested IF statements work from left to right and stop on the first true value. You are going to want to rearrange the order of your IF's so that the COUNTIFS is last.