Forum Discussion

Copper Contributor

Mar 04, 2020

Solved

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 ...

excel

ExcelExciting
Mar 04, 2020
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.

Mikalas

Copper Contributor

Mar 04, 2020

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

ExcelExciting

MVP

Mar 04, 2020

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.

Mikalas
Copper Contributor
Mar 04, 2020
ExcelExciting

This works! Thank you so much for your help! I really appreciate it!!!
- ExcelExciting
  MVP
  Mar 05, 2020
  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.