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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 04, 2020

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.

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

    HelloSergeiBaklan ,

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

Resources