Forum Discussion
How to remove rows with single value (multiple value remains)
Greetings! I need to remove students who only have 1 failing grade...and leave those students with 2 or more failing grades. The last name & first name fields will show the duplicate names and can be...
Your multiple fail formula could be simplified by returning only the second occurrence
= FILTER(A2:A21,B2:B21=2) †
or, as I would put it
= FILTER(fails, count=2)
Without the dynamic array functions, life gets more complicated
= IFERROR( INDEX( fails, SMALL( IF( COUNTIFS(fails, fails, k,"<="&k)=2, k ), k ) ), "" )
where 'k' is a unit based index column.
† Aside: I've realised that it is so many years since I last used a relative reference that it came as a shock to me that copying and pasting a formula to a new location could give different results!
In pre-DA row number if only care about sorting, otherwise simple LOOKUP
=IFNA(LOOKUP(2,1/($B$2:$B$21>1)/(COUNTIF($E$1:$E1,$A$2:$A$21)=0),$A$2:$A$21),"")