☰

Preciso extrair números não correspondentes de 2 colunas do Excel para uma nova planilha

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Preciso extrair números não correspondentes de 2 colunas do Excel para uma nova planilha

Tenho 2 colunas grandes (139.456 registros) por coluna. Preciso extrair os registros não correspondentes para uma nova planilha "folha de chamada". Estarei adicionando ao final de ambas as listas com o passar do tempo. Eu gostaria que ele publicasse os registros não correspondentes na parte inferior da "folha de chamadas". Cheguei perto de terminar, mas o Excel parece estar sobrecarregado. Continuo recebendo o erro de não 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 devido ao número de registros

Responder1

Eu manipulei um código antigo meu para retirar todos os dados da "Coluna 1" quenãocombine quaisquer dados na "Coluna 2" e imprima-os em outra planilha.
Talvez possa ser útil, talvez seja necessário ajustá-lo um pouco.

Começa com as variáveis ​​sobre onde estão suas listas e para onde você deseja que a nova lista vá – talvez você queira fazer essas variáveis, para crescer com as colunas.

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

Não é muito rápido, mas pelo menos não resulta em erro. Tentei com 45.000 entradas na Coluna 1 e 400 na Coluna 2, e isso levou cerca de6 segundospara gerar a lista.

Aviso Tentei com 220 mil linhas, verificando 100 mil linhas. E depois de 15 minutos, ele ainda está funcionando. Então, sim, se você quiser usar isso, espero que só precise executá-lo uma vez.

Você poderia torná-lo automático, mas provavelmente desejará um método mais rápido ou que observe apenas o último valor agregado.
Observe também que isso adicionará tudo duas vezes se for executado duas vezes. Não limpa a lista primeiro.

EDITAR 2

Uma maneira muito mais rápida de fazer isso seria uma macro que transformasse em uma tabela, classificasse os dados relevantes, copiasse os dados e depois removesse a tabela. Ele gerenciou todas as 220.000 entradas em questão de segundos. Eu só preciso descobrir como copiar as coisas que NÃO correspondem à lista, e não o contrário.

Editar 3

Ainda não descobri o material do filtro automático. Mas se você não usou esse outro código e ainda deseja usá-lo, use este:

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

É basicamente a mesma coisa, mas primeiro converte os dados em arrays e depois os usa para percorrê-los. Ainda é lento, mas é pelo menos 20 vezes mais rápido, se não mais. Gerenciei entradas de 220 mil x 220 mil em menos de 2 minutos.

Editar 4

Ok, então fiz uma solução alternativa no Filtro Automático.
Problemas:
Pode apenasmostraros valores no meu filtro, não *oculte-os.
Só pode trabalhar com os valores mostrados.
Não é possível excluir linhas com ou sem dados (muito lento).

Solução:
Então aqui está o que o novo código faz:
Primeiro, ele transforma o intervalo com o qual queremos trabalhar – Coluna "A" – em duas novas colunas, apenas para ter certeza de que não bagunçará a lista original.
Em seguida, ele faz a primeira cópia em uma tabela e a filtra com nosso segundo intervalo – Coluna “B”.
Em seguida, limpa o conteúdo de todas as células visíveis da tabela e remove a tabela.
Agora a primeira cópia contém apenas os dados que queremos, e um monte de buracos, onde apagou todos os dados indesejados. Então agora fazemos desse intervalo nosso novo filtro.
Agora a segunda cópia se torna uma tabela e é classificada com o novo filtro.
Em seguida, as células visíveis – agora os dados que queremos – são copiadas para outra coluna.

Atualmente, o código faz tudo na mesma planilha. E ocupa a coluna Maté Q. Portanto, tome cuidado ao testá-lo, pois isso vai atrapalhar se houver outros dados lá e provavelmente também se houver algum tipo de classificação e linhas ocultas na planilha.

Deve haver uma maneira melhor de escrever o código real, mas isso é tão bom quanto tenho tempo. Ele conseguiu executar a configuração atual (225.000 linhas de dados, 100.000 parâmetros para filtro) em12 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

informação relacionada