I have got a pivot table data as below.
Pivot Table Data:
| Row Labels | CAPACITY | DEMAND | Capacity-Demand | SurplusResourceUtilization |
|---|---|---|---|---|
| Resource 1 | 1600 | 1100 | 500 | TRUE |
| Resource 10 | 1800 | 1500 | 300 | TRUE |
| Resource 11 | 1600 | 950 | 650 | TRUE |
| Resource 2 | 1800 | 1200 | 600 | TRUE |
| Resource 3 | 800 | 400 | 400 | TRUE |
| Resource 4 | 200 | 300 | -100 | |
| Resource 5 | 800 | 900 | -100 | |
| Resource 6 | 900 | 200 | 700 | TRUE |
| Resource 7 | 250 | 400 | -150 | |
| Resource 8 | 900 | 800 | 100 | TRUE |
| Resource 9 | 800 | 900 | -100 |
I am trying to show top 5 records where my "SurplusResourceUtilization" column has value "True" or "Capacity-Demand" value greater than (>) 0 .
I tried using both below formulas.
=IFERROR(SORT(UNIQUE(FILTER(Resources!A3:D15,Resources!E3:E15="TRUE")),4,-1),"")
and
=IFERROR(SORT(UNIQUE(FILTER(Resources!A3:D15,SUMIF(Resources!A3:A15,Resources!A3:A15,Resources!D3:D15)>0))),"")
But both are giving all the records matching, in my case it is returning 7 records. Seems I am missing some matching criteria.
CodePudding user response：
Try:
=IFERROR(INDEX(SORT(FILTER(A3:D15,(D3:D15>0) (E3:E15=TRUE),""),4,-1),{1;2;3;4;5},{1,2,3,4}),"")