Filter OpenOffice Calc rows by matching if a cell in one column exists in another column with a certain value

Question

Have a question I cant seem to figure out using the filter.

Column A has 1400 rows with 800 additional rows that are duplicates (so 2200 total rows).

Column B has inventory amount for only the last 800 rows of Column A.

I want to filter my data so I only select rows where there is a duplicate value for Column A and my inventory amount in Column B is greater than 0 (i.e. one of those last 800 rows)

Answer 1

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

Answer 2

I suggest you add one column and then use Autofilter after that.

The added column should then contain "flags" for duplicates, if the table starts at line 2 the formula will be:

=SUM(IF($A$2:$A$9999=A2,1,0))
edit: Realized that by adding >1 at the end here you will get "TRUE" / "FALSE" flags instead. It is up to you.

and that needs to be entered in the cell with CTRL+SHIFT+ENTER
This will make it read with { and } at the ends as you VIEW the formula in the formula bar.

These cells will contain the COUNT of that value in the column, so: duplicates are >1

Copy the formula to the other lines... then use the Autofilter features for the remainder of the filtering.

NOTE: LibreOffice requires a change to the cell contents for re-entering it, add a space at the end