Eliminating Records that don't appear multiple times

[Mikalas]

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

[#2]

HI @Mikalas;

Please share few sample records & this can be possible..

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

If you find the above solution resolved your query don't forget mark as Official Answer.

[#3]

If you find the above solution resolved your query don't forget mark as Official Answer.

[#4]

Hello!@Faraz Shaikh 

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.

[#5]

@Mikalas 

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.

[#6]

@Mikalas 

Try this.  Conditional formatting with a COUNTIF.  Filter by color to isolate and remove the yellow records

[#7]

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)>=5

Please find below the sample file for your ready reference.

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

If you find the above solution resolved your query don't forget mark as Official Answer.

[#8]

If you find the above solution resolved your query don't forget mark as Official Answer.

[#9]

Hello@Sergei Baklan ,

I'm not that good with formulas.  Could you please write out the countif statement that I should use?  I'd appreciate it!

[#10]

@Faraz Shaikh 

Thank you for the detailed instructions.  I will try this and hopefully I can get it to work!  This would be a huge life saver!

[#11]

Hi Faraz, @Mikalas 

I tried the Countif statement but the issue is if I filter by >5 it would remove records that I need since I need to keep all the rows associated with those IDs that have 5 or more instances.  Is there a way to filter by this scenario?  So essentially I would need to keep all the rows that have the type of Cat and Dog since they appear 5+ times and remove all those other records that do not meet 5 or more instances.

Michelle

[#12 · Accepted solution]

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

If you find the above solution resolved your query don't forget mark as Official Answer.

[#13 · Accepted solution]

If you find the above solution resolved your query don't forget mark as Official Answer.

[#14]

@Faraz Shaikh 

This works!  Thank you so much for your help!  I really appreciate it!!!

[#15]

@Mikalas Glad to hear your query was resolved =)

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

If you find the above solution resolved your query don't forget mark as Official Answer.

[#16]

If you find the above solution resolved your query don't forget mark as Official Answer.