Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 at 21:26

2 Answers 2

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 at 18:33
    
I had ` ; ` in there, changed to ` , ` now did that help? –  Hannu Jul 21 at 18:37
    
... you didn't have a ';' in your original suggestion?!? I can't even use =(a2=c2) –  chris Jul 21 at 18:43
    
the second suggestion =IF((A2=C2),"DUPE","-") gives Err:508 –  chris Jul 21 at 18:44
    
You have some strange Excel then. Have a look above again. –  Hannu Jul 21 at 18:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.