Removing names which dont appear at least 4 times from a list in Excel

  • Hi all,


    I have attached the document for you to review and I'm struggling to work out how I would go about removing all companies names which don't appear at least 4 times in column A on the attached. So as an example 1313 Global would remain on the list but Altered States FX Limited in row 1288 would be removed as it only appears once, same goes for the next two companies after that.


    Once i have removed all companies that don't appear at least 4 times, I will then remove duplicates which i can do so i have a lsit of companies who have appeared at least 4 times in the list.


    Any advice would be appreciated.

    Files

  • You don't really need code. Just sort the data on the name column, then in G2 enter:


    =IF(A2=A1,TRUE,COUNTIF($A2:$A$28942,A2)<4)


    and copy down. Then filter for True on column G and delete those rows (or filter for False and copy the Names somewhere else).


    You could also do it with an advanced filter using a formula criterion.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why