Как удалить (или выделить) дублирующиеся строки в Excel ТОЛЬКО, если есть 4 или более экземпляров?

Question

У меня есть документ Excel, который содержит более 300 строк данных. Столбец А отсортирован по именам персонала, и мне нужно изолировать только тех людей, которые имеют менее 4 вхождений на листе.

Есть ли способ удалить все имена с 4 или более? Или выделить все имена 4 или более?

Спасибо!

Answer 1

Студент Гари: решение VBA для удаления всех, кроме <4 толпы
selwyth: Формула решения для фильтрации только для <4 толпы
This Answer: решение для условного форматирования, чтобы выделить только <4 толпы

(Вы также можете сослаться на статью поддержки Office об использовании формул в условном форматировании, который, как выясняется, использует COUNTIF в качестве примера.)

1. Выберите диапазон данных (в примере я выбрал все столбец A:A
2. На ленте Home нажмите Conditional Formatting а затем New Rule...
3. Выберите Use a formula to determine which cells to format
4. Введите формулу вроде =COUNTIF($A:$A,$A1)<4
   A. Измените $A:$A на любой диапазон, в котором вы ищете дубликаты. Например, это может быть $G$12:$G$48 . $ Важен, потому что он делает его абсолютной ссылкой , которая не меняется от ячейки к ячейке.
   B. Измените $A1 на первую ячейку в выбранном диапазоне. Обратите внимание, что здесь только один $ потому что мы хотим, чтобы столбец был абсолютным. Мы хотим , чтобы номер строки 1 в данном случае) , чтобы быть относительно текущей ячейки.
   C. Результат таков, что условное форматирование для A1 будет использовать формулу =COUNTIF($A:$A,$A1)<4 но для A2 оно будет использовать формулу =COUNTIF($A:$A,$A2)<4 . Это то, что контролирует абсолютное и относительное рефенсы ($ или нет $).
5. Нажмите кнопку « Format... и выберите форматирование, которое вам нравится. В этом случае я решил выделить ячейки на желтом фоне.
6. Нажмите кнопку OK чтобы закрыть окно условного форматирования и увидеть результаты

Обратите внимание, что из-за того, как я это сделал, все пустые строки тоже подсвечиваются. Если это не работает для вас, есть несколько способов исправить это. Я рекомендую Вариант 1, потому что это простой способ следить за вашим списком по мере его роста.

1. Измените формулу, которую вы используете на шаге 4, на =AND($A1<>"",COUNTIF($A:$A,$A1)<4)
2. Вместо выбора всего столбца A:A как я делал на шаге 1, выберите только свой диапазон данных.
3. После создания условного форматирования вернитесь в Conditional Formatting Rules Manager (Главная лента> Условное форматирование> Управление правилами ...) и измените поле Applies to =$A:$A на что-то конкретное, например =$A$1:$A$19 . Это тот же результат, что и при использовании варианта 2, но он позволяет изменить его после факта.

Answer 2

Для решения не VBA:

Поместите формулу наподобие =countif($A:$A,$A1) в столбец B, теперь ваша таблица будет выглядеть примерно так:

    A    B  
1  Joe   3  
2  Joe   3  
3  Joe   3  
4  Amy   5  
5  Amy   5  
6  Amy   5  
7  Amy   5
8  Amy   5

Затем выберите A1 , перейдите к условному форматированию (пользовательское правило) и вставьте в формулу =B1>=4 . Затем применить правило к стольким , сколько вам нужно в столбце A. Отсутствие $ якоря означает , что правило будет двигаться вместе с приложенной клеткой.

Answer 3

Скажем, мы начнем с:

и мы запускаем этот короткий макрос:

Sub RowKiller101()
  Dim rKill As Range, r As Range, wf As WorksheetFunction
  Dim rBig As Range
  Set rBig = Intersect(Range("A:A"), ActiveSheet.UsedRange)
  Set rKill = Nothing
  Set wf = Application.WorksheetFunction

  For Each r In rBig
    If wf.CountIf(rBig, r.Value) > 3 Then
      If rKill Is Nothing Then
        Set rKill = r
      Else
        Set rKill = Union(rKill, r)
      End If
    End If
  Next r

  If rKill Is Nothing Then Exit Sub
rKill.EntireRow.Delete
End Sub

Мы получим: