Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question
    
Dunno about Calc, but with Excel you could use the following in a "helper column" (example formula for row 1): =AND(COUNTIF(A:A,A1)>1,B1>0). Then, filter the sheet to only show rows where that column's value is TRUE. – Iszi Jul 29 '14 at 21:26
up vote 0 down vote accepted

Instead of using a array formula, you could use the COUNTIF() function to check for duplicates. Assuming this sample data:

enter image description here

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:

enter image description here

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:

enter image description here

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:

enter image description here

(tested with LO Calc - should work the same way using OOo Calc).

share|improve this answer

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

share|improve this answer
    
I get Err:522 when I try that. I don't see how that would help me. My duplicates are not in a separate column, they are in the SAME column. – chris Jul 21 '14 at 18:33
    
I had ` ; ` in there, changed to ` , ` now did that help? – Hannu Jul 21 '14 at 18:37
    
... you didn't have a ';' in your original suggestion?!? I can't even use =(a2=c2) – chris Jul 21 '14 at 18:43
    
the second suggestion =IF((A2=C2),"DUPE","-") gives Err:508 – chris Jul 21 '14 at 18:44
    
You have some strange Excel then. Have a look above again. – Hannu Jul 21 '14 at 18:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .