FORMULA TO FIND DUPLICATES

[Joe H.]

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!

 

[JE McGimpsey]

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

One way (array-entered with CMD-RETURN):


B1: =IF(SUMPRODUCT(--(ABS($A$1:$A$6)=ABS(A1)))>1,"duplicate","")

Copy down to B6

 

[JE McGimpsey]

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....

 

[Joe H.]

Is there a way to not have "dulplicate" print next to blank cells?

 

[JE McGimpsey]

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", "")

 

[Joe H.]

I had to change the "A9" to "A1" and it worked fine. Thanks for your help!