Forum Discussion
How to remove rows with single value (multiple value remains)
MsV213 I would suggest not to remove data but just filter out what you don't want to see. If you are on a recent Excel version it perhaps supports the UNIQUE and FILTER functions. If not, a Pivot Table could do what you want. You'll find working examples for both in the attached workbook.
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),"")
