Duplicate Check Formula
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
Please help, Thanks
Answers
-
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)
-
@Paul Newcome Is correct. That is how I would do it.
-
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 :
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!