G Guest Jan 13, 2006 #1 I have a huge list of telephone numbers and I have to filter out the ones that appear more than 5 times, any ideas
I have a huge list of telephone numbers and I have to filter out the ones that appear more than 5 times, any ideas
D Dave Peterson Jan 13, 2006 #2 If your data is in column A, you can put this in column B: =countif(a:a,a1) and drag down Then apply data|filter|autofilter to column B. Custom filter greater than 5 And delete the visible rows. This will delete all the numbers that appear more than 5 times. If you only wanted to keep up to the first 5 instances, you could use a formula like: =countif($a$1:a1,a1) and drag down. And still filter to show the counts greater than 5.
If your data is in column A, you can put this in column B: =countif(a:a,a1) and drag down Then apply data|filter|autofilter to column B. Custom filter greater than 5 And delete the visible rows. This will delete all the numbers that appear more than 5 times. If you only wanted to keep up to the first 5 instances, you could use a formula like: =countif($a$1:a1,a1) and drag down. And still filter to show the counts greater than 5.
C CLR Jan 13, 2006 #3 I would sort the collumn ascending, assuming it to be A, then in helper column B6 put this and copy down..... =IF(A6=A1,"Delete Me","") Copy > PasteSpecial > Values on column B Then sort on column B and delete the rows indicated Vaya con Dios, Chuck, CABGx3
I would sort the collumn ascending, assuming it to be A, then in helper column B6 put this and copy down..... =IF(A6=A1,"Delete Me","") Copy > PasteSpecial > Values on column B Then sort on column B and delete the rows indicated Vaya con Dios, Chuck, CABGx3
