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.
- Brilliant!!! It's amazing how you overthink when you face a new challenge...but always good to be reminded of features to use in tough situations. Thanks so much for responding and providing the workbook to backup the tip you shared!!! I'm on my way!!!! I'm very grateful!!!
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),"")
