Instead of using a array formula, you could use the COUNTIF()
function to check for duplicates. Assuming this sample data:
You can add a flag in Column C
indicating duplicates in Col. A
using this formula in C2
, and copying it down:
=(COUNTIF($A$2:$A$13, A2)>1)
This results in:
Now, you can add a second "flag column", combining the boolean value of col C
with a check for B>0
:
=AND(C2,B2>0)
Now, we have:
Of course, you could combine both formulae - the result (again for row 2
) may look like:
=AND((COUNTIF($A$2:$A$13,A2)>1),B2>0)
As result, you have an additional column with a TRUE
flag for all rows that are duplicates and have an amount > 0
. Now, you can apply an AutoFilter
(Menu "Date
" -> "Filter
" -> "AutoFilter
") and filter for rows with that TRUE
flag:
(tested with LO Calc - should work the same way using OOo Calc).
=AND(COUNTIF(A:A,A1)>1,B1>0)
. Then, filter the sheet to only show rows where that column's value is TRUE.