Forum Discussion
How to remove rows with single value (multiple value remains)
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 used as part of the criteria. Is there a way to automatically remove/hide the students with 1 failing grade (name appears once)? I look forward to your reply.
4 Replies
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),"")
