Como excluo (ou realço) linhas duplicadas no Excel SOMENTE SE houver 4 ou mais instâncias?

Question

Eu tenho um documento Excel que contém mais de 300 linhas de dados. A coluna A é classificada por nomes de pessoal e preciso isolar apenas as pessoas que têmmenos de 4ocorrências dentro da planilha.

Existe uma maneira de excluir todos os nomes com 4 ou mais? Ou destacar todos os nomes com 4 ou mais?

Obrigado!

Answer 1

Digamos que começamos com:

e executamos esta pequena macro:

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

Nós conseguiremos:

Answer 2

Para uma solução não VBA:

Coloque uma fórmula como =countif($A:$A,$A1)na coluna B, sua planilha agora ficará mais ou menos assim:

    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

Depois selecione A1, vá em Formatação Condicional (regra personalizada) e coloque a fórmula =B1>=4. Em seguida, aplique a regra o quanto for necessário na coluna A. A falta de uma $âncora significa que a regra se moverá junto com a célula aplicada.

Answer 3

Aluno de Gary: Solução VBA para excluir todos, exceto a multidão <4
selwyth: Solução de fórmula para filtrar apenas a multidão <4
Esta resposta: Solução de formatação condicional para destacar apenas a multidão <4

(Você também pode consultar oArtigo de suporte do Officesobre o uso de fórmulas em formatação condicional que, como se vê, usa COUNTIFcomo exemplo.)

1. Selecione o intervalo de dados (no exemplo, selecionei todas as colunasA:A
2. Na Homefaixa de opções, clique Conditional Formattinge depoisNew Rule...
3. SelecioneUse a formula to determine which cells to format
4. Insira uma fórmula como =COUNTIF($A:$A,$A1)<4
   A. Altere $A:$Apara qualquer intervalo em que você está procurando duplicatas. Por exemplo, poderia ser $G$12:$G$48. O $é importante porque o torna umreferência absolutaisso não muda de célula para célula.
   B. Mude $A1para oprimeirocélula no intervalo selecionado. Observe que há apenas um $aqui porque queremos que a coluna seja absoluta. Queremos que o número da linha ( 1, neste caso) seja relativo à célula atual.
   C. O resultado é tal que a formatação condicional para A1usará a fórmula =COUNTIF($A:$A,$A1)<4, mas, para A2, usará a fórmula =COUNTIF($A:$A,$A2)<4. Isso é o que controlar as referências absolutas e relativas ( $ou não $) faz por você.
5. Clique no Format...botão e selecione a formatação desejada. Neste caso, optei por destacar as células em fundo amarelo.
6. Clique OKpara fechar a janela de formatação condicional e ver os resultados

Observe que, devido à maneira como fiz isso, todas as linhas em branco também são destacadas. Se isso não funcionar para você, existem algumas maneiras de consertar. Eu recomendo a Opção 1 porque é uma maneira simples de acompanhar sua lista à medida que ela cresce.

1. Altere a fórmula usada na Etapa 4 para=AND($A1<>"",COUNTIF($A:$A,$A1)<4)
2. Em vez de selecionar todas as colunas A:Acomo fiz na Etapa 1, selecione apenas o intervalo de dados.
3. Depois que a formatação condicional for criada, volte para Conditional Formatting Rules Manager(faixa inicial > Formatação condicional > Gerenciar regras...) e altere o Applies tocampo de =$A:$Apara algo específico como =$A$1:$A$19. Este é o mesmo resultado de usar a Opção 2, mas permite alterá-lo após o fato.