excelexcel-formulaexcel-2016

How do I filter data to only include Item Numbers where there is a negative value present in Projected Quantity column?

I need to only return rows that have Item Number 67, as there is negative Projected Quantity values here. I need to return all rows with Item Number 67, not just rows with a negative Projected Quantity value. If an extra column needs to added to insert a formula that's no problem. I have looked at advanced filter options and cannot see how this can be done. Any help is really appreciated.

Start Date Item Number Projected Quantity
05/12/2024 45 51866
06/12/2024 45 51866
09/12/2024 45 51241
10/12/2024 45 51235
19/12/2024 45 51235
19/11/2024 67 1000
19/11/2024 67 500
20/11/2024 67 -250
21/11/2024 67 -500
15/11/2024 111 404
25/11/2024 111 550
26/11/2024 111 2470
27/11/2024 111 5678
Solution

  • Try using INDEX()+AGGREGATE() functions here:

    enter image description here

    • Formula used in cell E2

    =IFERROR(INDEX(A$2:A$14,
 AGGREGATE(15,7,(ROW($B$2:$B$14)-ROW($B$2)+1)/
 (COUNTIFS($B$2:$B$14,$B$2:$B$14,$C$2:$C$14,"<0")>0),
 ROWS(E$2:E2))),"")

    Formula needs to copy down and copy right.

    Another alternative method:

    =IFERROR(INDEX($A$1:$C$14,
 AGGREGATE(15,6,ROW($B$2:$B$14)/
 ($B$2:$B$14=AGGREGATE(15,6,IF($C$2:$C$14<0,$B$2:$B$14,1/0),1)),ROW($A1)),
 COLUMN(A$1)),"")