☰

Necesito extraer números que no coincidan de 2 columnas de Excel en una hoja nueva

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Necesito extraer números que no coincidan de 2 columnas de Excel en una hoja nueva

Tengo 2 columnas grandes (139456 registros) por columna. Necesito extraer los registros que no coinciden a una nueva hoja "hoja de llamadas". Agregaré al final de ambas listas a medida que pase el tiempo. Me gustaría que agregue los registros que no coinciden al final de la "hoja de llamadas". Estoy a punto de terminarlo, pero Excel parece estar sobrecargado. Sigo recibiendo el error de no responder.

=IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")

Sobrecargas por número de registros

Respuesta1

Yo manipulé un viejo código mío para sacar todos los datos de la "Columna 1" quenohaga coincidir cualquier dato en la "Columna 2" e imprímalo en otra hoja.
Tal vez pueda ser de alguna utilidad, es posible que tengas que modificarlo un poco.

Comienza con las variables sobre dónde están sus listas y dónde desea que vaya la nueva lista; es posible que desee hacer estas variables para crecer con las columnas.

Sub sort()
Dim list1 As Range, list2 As Range, c As Range, outSht As Worksheet, outCol As String, Lrow As Long
'---Options---
Set list1 = Range("A2:A1999")   'Range of first column
Set list2 = Range("B2:B399")    'Range of second column
Set outSht = Sheets("Sheet2")   'Output Sheet (Create one first)
outCol = "A"                    'Output Column

Application.ScreenUpdating = False
oCN = Columns(outCol).Column
For Each c In list1
    If list2.Find(c.Value) Is Nothing Then
        lRow = outSht.Range(outCol & ActiveSheet.Rows.Count).End(xlUp).Row
        outSht.Cells(lRow + 1, oCN).Value = c.Value
    End If
Next c
Application.ScreenUpdating = True
End Sub

No es muy rápido, pero al menos no termina en un error. Intenté con 45000 entradas en la Columna 1 y 400 en la Columna 2, y eso me llevó aproximadamente6 segundospara generar la lista.

Advertencia Probé con 220.000 filas comparándolas con 100.000 filas. Y después de 15 minutos, sigue funcionando. Así que sí, si quieres utilizar esto, espero que sólo tengas que ejecutarlo una vez.

Podría hacerlo automático, pero probablemente desee un método más rápido o uno que solo analice el último valor agregado.
También tenga en cuenta que esto simplemente agregará todo dos veces si se ejecuta dos veces. No borra la lista primero.

EDITAR 2

Una forma mucho más rápida de hacerlo sería una macro que la convierta en una tabla, ordene los datos relevantes, copie los datos y luego elimine la tabla. Gestionó las 220.000 entradas en cuestión de segundos. Sólo tengo que descubrir cómo copiar las cosas que NO coinciden con la lista, y no al revés.

Editar 3

Todavía tengo que descubrir el tema del autofiltro. Pero si no has usado ese otro código y aún quieres hacerlo, usa este en su lugar:

Sub ArrayIt()
Dim aArray As Variant, bArray As Variant
aArray = [transpose(A2:A139456)]
bArray = [transpose(B2:B139456)]
Set outSht = Sheets("Sheet2")   'Output Sheet (Create one first)
outCol = "A"                    'Output Column
Application.ScreenUpdating = False
oCN = Columns(outCol).Column
For Each c In aArray

    If IsError(Application.Match(c, bArray, 0)) Then
        Lrow = outSht.Range(outCol & outSht.Rows.Count).End(xlUp).Row
        outSht.Cells(Lrow + 1, oCN).Value = c   
    End If
Next c
Application.ScreenUpdating = True
End Sub

Es básicamente lo mismo, pero primero convierte los datos en matrices y luego los usa para revisarlos. Sigue siendo lento, pero es al menos 20 veces más rápido, si no más. Gestioné 220k x 220k entradas en menos de 2 minutos.

Editar 4

Bien, hice una solución alternativa al Autofiltro.
Asuntos:
Sólo podemosespectáculolos valores en mi filtro, no *ocultarlos.
Sólo se puede trabajar con los valores que se muestran.
No se pueden eliminar filas con o sin datos (demasiado lento).

Solución:
Entonces, esto es lo que hace el nuevo código:
primero, copia el rango con el que queremos trabajar (columna "A") en dos columnas nuevas, solo para asegurarnos de que no estropee la lista original.
Luego hace la primera copia en una tabla y la filtra con nuestro segundo rango: la columna "B".
Luego, borra el contenido de cada celda visible de la tabla y elimina la tabla.
Ahora la primera copia contiene solo los datos que queremos y un montón de agujeros, donde se borran todos los datos no deseados. Así que ahora hacemos de ese rango nuestro nuevo filtro.
Ahora la segunda copia se convierte en una tabla y se ordena con el nuevo filtro.
Luego, las celdas visibles (ahora los datos que queremos) se copian en otra columna.

Actualmente, el código lo hace todo en la misma hoja. Y ocupa una columna Mpara Q. Así que tenga cuidado cuando lo pruebe, ya que se estropeará si hay otros datos allí, y probablemente también si hay algún tipo de clasificación y filas ocultas en la hoja.

Seguramente habrá una mejor manera de escribir el código real, pero esta es la mejor manera con la que tengo tiempo. Logró ejecutar la configuración actual (225 000 filas de datos, 100 000 parámetros para el filtro) en12 segundos.

Sub aaTablefiltering()
Dim LO As ListObject, tName As String, rOne As Range, rTwo As Range, rThree As Range, rFour As Range, fArr As Variant

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

Set rOne = Range("A2:A225000")
Set rTwo = Range("B2:B100000")
Set rThree = Range("M2:M225001")
Set fFour = Range("O2:O225001")
fArr = [transpose(B2:B100000)]
tName = "DTable"

rOne.Copy Destination:=Range("M2")
rOne.Copy Destination:=Range("O2")

Set LO = ActiveSheet.ListObjects.Add(xlSrcRange, rThree, , xlNo)
LO.Name = tName
ActiveSheet.ListObjects("DTable").Range.AutoFilter Field:=1, Criteria1:=fArr, Operator:=xlFilterValues
ActiveSheet.ListObjects("DTable").Range.SpecialCells(xlCellTypeVisible).ClearContents
ActiveSheet.ListObjects("DTable").Unlist
fArr = [transpose(M2:M225001)]

Set LO = ActiveSheet.ListObjects.Add(xlSrcRange, fFour, , xlNo)
LO.Name = tName
ActiveSheet.ListObjects("DTable").Range.AutoFilter Field:=1, Criteria1:=fArr, Operator:=xlFilterValues
ActiveSheet.ListObjects("DTable").Range.SpecialCells(xlCellTypeVisible).Copy _
    Destination:=ActiveSheet.Range("Q1")
ActiveSheet.ListObjects("DTable").Unlist
Range("M:Q").ClearFormats
Range("M:O").ClearContents

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

información relacionada