Forum Discussion

Mikalas's avatar
Mikalas
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's avatar
    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.

ExcelExciting's avatar
ExcelExciting
MVP
Mar 04, 2020

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.

 

Mikalas's avatar
Mikalas
Copper Contributor
Mar 04, 2020

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.

 

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Mar 04, 2020

    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.

     

     

     

     

    • Mikalas's avatar
      Mikalas
      Copper Contributor
      Mar 04, 2020

      ExcelExciting 

      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!

      • Mikalas's avatar
        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

Resources