 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / August 2007FORMULA TO FIND DUPLICATES
I need find a formula to identify duplicate numbers in a column being either a debit or a credit. For example: A B 1234.00 duplicate 2345.00 3456.00 duplicate 4567.00 -1234.00 duplicate -3456.00 duplicate Thanks! > I need find a formula to identify duplicate numbers in a column being either > a debit or a credit. For example: [quoted text clipped - 5 lines] > -1234.00 duplicate > -3456.00 duplicate One way (array-entered with CMD-RETURN): B1: =IF(SUMPRODUCT(--(ABS($A$1:$A$6)=ABS(A1)))>1,"duplicate","") Copy down to B6 > One way (array-entered with CMD-RETURN): > > B1: =IF(SUMPRODUCT(--(ABS($A$1:$A$6)=ABS(A1)))>1,"duplicate","") > > Copy down to B6 Oops - changed the formula, but didn't change the instruction: No array-entering needed.... Is there a way to not have "dulplicate" print next to blank cells? > > One way (array-entered with CMD-RETURN): > > [quoted text clipped - 5 lines] > > No array-entering needed.... > Is there a way to not have "dulplicate" print next to blank cells? One way: =IF(SUMPRODUCT(--($A$1:$A$6<>""),--(ABS($A$1:$A$6)=ABS(A9)))>1, "duplicate", "") I had to change the "A9" to "A1" and it worked fine. Thanks for your help! > > Is there a way to not have "dulplicate" print next to blank cells? > > One way: > > =IF(SUMPRODUCT(--($A$1:$A$6<>""),--(ABS($A$1:$A$6)=ABS(A9)))>1, > "duplicate", "") |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.