una - ¿Cómo eliminar los duplicados que son SENSIBLES a mayúsculas/minúsculas en Excel(para 100k registros o más)?

formula para buscar datos repetidos en excel

Question

He intentado usar este campo de fórmula y copiar en todos> 100k registros que tengo.

= IF (SUMAPRODUCTO (- EXACT (A2, $ B $ 1: B1)), "", A2

dónde:

1. columna A = columna con todos los datos, incluidos los duplicados
2. columna B = columna para mostrar los datos (de la columna A) si la cadena está vacía de otra manera

Sin embargo, llegué a este problema:

Sí, mi Excel 2016 es de 32 bits y sí, mi computadora portátil tiene solo 8 GB de RAM. Pero he leído que las personas con 64 bits y 16 GB de RAM experimentaron el mismo error que yo.

Sé que hay una función en la función de Excel: Datos> Seleccionar columna (s)> Eliminar duplicados. Sin embargo, esta función borra solo los datos de INSENSITIVE del caso.

Por favor, avísenme cómo puedo superar este problema. Estoy abierto a usar cosas como Crystal Reports o algún tipo de software gratuito para resolver este problema. Por favor avise.

Answer 1

Para una solución general, es probable que prefiera el enfoque de VBA ya sugerido. Pero para algo que funciona solo una vez, probablemente puedas hacer que funcione de la manera que quisieras con solo un poco de adaptación en la forma en que aplicas =IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),"",A2) . También traté de usar un algoritmo COUNTIF, que es mucho más rápido que SUMPRODUCT, pero no distingue entre mayúsculas y minúsculas.

Como también estoy ejecutando Excel de 32 bits con memoria de 8GB, tenía curiosidad por ver si podía replicar el problema de la memoria. Genere una lista de 100.000 cadenas de 5 letras al azar en la columna A. Solo se usaron 10 letras (ABCDEFGHJK), por lo que en 100.000 cadenas, algunas se producirían más de una vez. Luego apliqué la fórmula sugerida por el OP en la columna B para filtrar solo valores únicos. Realmente funcionó, pero llevó bastante tiempo. Pero nunca me encontré con el problema de memoria que hizo el OP.

Solución propuesta:
Con base en estas observaciones, una posible solución a su problema particular podría ser copiar la columna A a un nuevo workbok temporal y ejecutar su fórmula SUMPRODUCT allí mientras todos los demás libros están cerrados. Una vez que haya terminado, puede pegar el resultado en la columna original en el archivo original. En realidad, eliminar los duplicados podría hacerse simplemente filtrando en esa columna para que todas las duplicaciones (celdas vacías) se agrupen y luego eliminen esas filas. Los detalles de mi intento de replicar se pueden encontrar a continuación.

SUMAPRODUCTO: Aproximadamente 1 hora
Primero probé la misma fórmula que en el OP, =IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),"",A2) , pero haciendo solo 10,000 filas a la vez (insertando filas vacías en fila 10,000, 20,000 etc. y copiando diez mil filas a la vez.) Cada conjunto de 10,000 filas tomó un par de minutos en completarse. Cuando hice todo el shebang como una operación de copia gigante para todas las 100.000 células a la vez, la operación tomó alrededor de una hora en completarse y Excel no respondió mientras tanto. El uso de memoria fue de 1,4 GB y la CPU promedió más del 50% de su capacidad (supervisada con el Administrador de tareas de Windows). También traté de ejecutar la fórmula cuando ya había manipulado los datos de varias maneras (consumiendo así más memoria), lo que aumentó la capacidad de la CPU al 100% y provocó un par de bloqueos. Logré evitar eso simplemente cerrando Excel para borrar la memoria y volver a ejecutar la operación desde un nuevo reinicio sin abrir otros libros de trabajo.

Como puede ver en las siguientes capturas de pantalla, la fórmula funcionó y las entradas únicas se vuelven más raras en la lista (como se esperaba, ya que son aleatorias). Asigné 1 a las celdas que contienen duplicados para poder contarlos fácilmente. Hubo 36,843 de esos casos.

Primeras filas, sin duplicados:

Últimas filas, en su mayoría duplicados (celdas con 1):

CONTEO: 8.5 minutos
En comparación con el algoritmo SUMPRODUCT, que tardó alrededor de una hora en completarse, la siguiente fórmula COUNTIF completó el mismo trabajo en solo 8,5 minutos, pero no distinguió entre mayúsculas y minúsculas. Este enfoque requiere el uso de una columna de ayuda. COUNTIF devuelve el número de instancias en que se ha utilizado una cadena en particular en el rango superior a la celda actual, por lo que cada vez que se encuentra una cadena por primera vez, devolverá 1. La celda B2 contiene =COUNTIF($A$2:$A2,A2) , y copiar esto para todas las 100.000 filas tomó alrededor de ocho minutos y medio. Luego, en una columna separada, utilicé una fórmula IF simple para filtrar los valores únicos de la columna A; la celda C2 contiene =IF(B2=1,A2,1) , que devuelve la cadena en la columna A si es única; de lo contrario, se devuelve 1 (para permitir una comparación fácil con SUMPRODUCT). Copiar esta fórmula IF para todas las 100.000 filas es prácticamente instantánea. La suma de 1s en la columna C después de esta operación fue, de manera tranquilizadora, la misma que en el caso de SUMPRODUCT, 36,843.

ÍNDICE: Fracaso
También jugué con una fórmula de matriz usando las funciones INDEX y MATCH. Esta fórmula hace el mismo trabajo que COUNTIF, pero también filtra las filas vacías: =INDEX($A$2:$A$100001,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$100001),0)) . Esto debe ingresarse en la celda B2 como una fórmula de matriz (Ctrl + Shift + Intro) y luego copiarse hacia abajo. Copiar celdas individuales de a una por vez funcionó bien durante unas pocas docenas de filas, pero nada más que eso hizo que Excel fallara. Incluso traté de ejecutar esto de la noche a la mañana, pero la operación nunca terminó. (La fórmula podría ampliarse para ser sensible a mayúsculas y minúsculas, pero no me molesté en intentarlo).

Una cosa a tener en cuenta, sin embargo, con la fórmula INDEX fallida fue que el comportamiento descrito anteriormente se produjo cuando la fórmula se aplicó en un libro de trabajo por separado . También traté de ejecutar esta fórmula en la columna D en el mismo libro de trabajo que la fórmula COUNTIF. Entonces me encontré con el problema de memoria descrito en el OP, que, como era de esperar, sugiere que el problema con la memoria depende del resto de los datos en el libro de trabajo.

Answer 2

Puede intentar algo como esto. Antes de intentar esta copia de seguridad de tus datos. El siguiente código eliminará los duplicados de la columna A y distingue entre mayúsculas y minúsculas.

Sub GetUniqueValues()
Dim x, dict
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("A2:A" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = ""
Next i
Range("A2:A" & lr).ClearContents
Range("A2").Resize(dict.Count).Value = Application.Transpose(dict.keys)
End Sub

Código editado:

Sub GetUniqueValues()
Dim x, dict, y
Dim lr As Long
Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("A2:A" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = ""
Next i
ReDim y(1 To dict.Count, 1 To 1)

i = 0
For Each it In dict.keys
   i = i + 1
   y(i, 1) = it
Next it

Range("A2:A" & lr).ClearContents
Range("A2").Resize(dict.Count).Value = y

Application.ScreenUpdating = True
End Sub