¿Cómo elimino (o resalto) filas duplicadas en Excel SÓLO SI hay 4 o más instancias?

Question

Tengo un documento de Excel que tiene más de 300 filas de datos. La columna A está ordenada por nombres de personal y necesito aislar sólo a las personas que tienenmenos de 4ocurrencias dentro de la hoja.

¿Hay alguna manera de eliminar todos los nombres con 4 o más? ¿O resaltar todos los nombres con 4 o más?

¡Gracias!

Answer 1

Digamos que comenzamos con:

y ejecutamos esta breve 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

Obtendremos:

Answer 2

Para una solución que no sea VBA:

Coloque una fórmula como =countif($A:$A,$A1)en la columna B, su hoja de cálculo ahora se verá así:

    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

Luego seleccione A1, vaya a Formato condicional (regla personalizada) y escriba la fórmula =B1>=4. Luego aplique la regla a todo lo que necesite en la columna A. La falta de un $ancla significa que la regla se moverá junto con la celda aplicada.

Answer 3

Estudiante de Gary: Solución de VBA para eliminar todo menos el grupo <4
selwyth: Solución de fórmula para filtrar solo al grupo <4
Esta respuesta: Solución de formato condicional para resaltar solo el grupo <4

(También puede consultar elArtículo de soporte de Officesobre el uso de fórmulas en formato condicional que, como resulta, se utiliza COUNTIFcomo ejemplo).

1. Seleccione el rango de datos (en el ejemplo, seleccioné todas las columnasA:A
2. En la Homecinta, haga clic Conditional Formattingy luegoNew Rule...
3. SeleccionarUse a formula to determine which cells to format
4. Ingrese una fórmula como =COUNTIF($A:$A,$A1)<4
   A. Cambie $A:$Aa cualquier rango en el que esté buscando duplicados. Por ejemplo, podría ser $G$12:$G$48. Es $importante porque lo convierte en unreferencia absolutaeso no cambia de una celda a otra.
   B. Cambie el $A1alprimerocelda en el rango seleccionado. Tenga en cuenta que aquí solo hay uno $porque solo queremos que la columna sea absoluta. Queremos que el número de fila ( 1, en este caso) sea relativo a la celda actual.
   C. El resultado es tal que el formato condicional para A1usará la fórmula =COUNTIF($A:$A,$A1)<4pero, para A2, usará la fórmula =COUNTIF($A:$A,$A2)<4. Esto es lo que hace por usted controlar las referencias absolutas versus relativas ( $o no ).$
5. Haga clic en el Format...botón y seleccione el formato que desee. En este caso, elegí resaltar las celdas con un fondo amarillo.
6. Haga clic OKpara cerrar la ventana de formato condicional y ver los resultados.

Tenga en cuenta que, debido a la forma en que hice esto, todas las filas en blanco también están resaltadas. Si eso no funciona para usted, existen algunas formas de solucionarlo. Recomiendo la Opción 1 porque es una forma sencilla de mantenerse al día con su lista a medida que crece.

1. Cambie la fórmula que utiliza en el Paso 4 a=AND($A1<>"",COUNTIF($A:$A,$A1)<4)
2. En lugar de seleccionar todas las columnas A:Acomo hice en el Paso 1, seleccione solo su rango de datos.
3. Una vez creado el formato condicional, regrese a Conditional Formatting Rules Manager(cinta Inicio > Formato condicional > Administrar reglas...) y cambie el Applies tocampo =$A:$Aa algo específico como =$A$1:$A$19. Este es el mismo resultado que usando la Opción 2 pero le permite cambiarlo después del hecho.