Forum Discussion
Eliminating Records that don't appear multiple times
Hello,
I have a file with multiple records on it and I'm looking to only keep those records in which an ID # appears 5 or more times. Is there a way to filter these out and/or delete those records that don't appear at least 5 times? My files has about 20,000 records in it. I've sorted by ID and highlighted duplicate values but there are many records in which the ID only appears once or 2 or 3 times and I need to delete those records. Hoping someone can help! Thank you.
Mikalas
Hi..
Use this formula.. you will get the results as TRUE & FALSE.. just filter TRUE using filter=COUNTIF($B$2:$B$24,B2)>=5
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
11 Replies
You may add helper column with COUNTIF() ID for each record, after that filter on this column with values more than 5, Or, in opposite, less than 5 and delete filtered records.
HelloSergeiBaklan ,
I'm not that good with formulas. Could you please write out the countif statement that I should use? I'd appreciate it!
HI Mikalas;
Please share few sample records & this can be possible..
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
Hello!ExcelExciting
Here is a dummy file as I couldn't send the real info. So in this example I would like to keep in the file those records that have the Type of Cat and Dog since they appear at least 5 times. I'd want to delete all the other rows since they didn't NOT appear at least 5 times. I hope that made sense.
HI Mikalas,
There are two different solution you can handle this kind of data
Solution No 01
Create an helper column and paste the below formula
=COUNTIF($B$2:$B$24,B2)that will generate the count of items, you an use filter > than 5 and copy the list two another location
Solution No 02
If you are familiar using advance filter the create the logic.
=COUNTIF($B$2:$B$24,B2)>=5Please find below the sample file for your ready reference.
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com
