Atualmente tenho duas colunas que precisam ser comparadas. Coluna A, Folha 1 e Coluna A, Folha 2.
A folha 1 contém:
A B C
5000 Apples WI
6182 Oranges NY
7271 Grapes MN
2293 Peanuts FL
A folha 2 contém:
A
4032
5233
7271
2293
Deve receber resultados como....
7271 Grapes MN
2293 Peanuts FL
Preciso exibir apenas os resultados que contêm o mesmo número da Planilha 2. Existe uma maneira melhor do que carregar a Planilha 2 em uma matriz e compará-la com cada célula da Planilha 1?
'For i = 1 to Sheet1LastRow
Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)
'if cell = Sheet2Array(i, 1)
'....
'End if
Next i
'Next Cell sheet 1
Responder1
Esta é a função central do =VLOOKUP().
A sintaxe é:
=VLOOKUP(
compare this cell,
to the cells in the leftmost column of this range,
returning the corresponding value from this column index,
true/false for range lookup (just leave this false if you aren't sure)
)
Na planilha 2, na célula B1 você colocaria =VLOOKUP(A1,Sheet1!A:B,2,False)e na C1 você colocaria=VLOOKUP(A1,Sheet1!A:C,3,False)
Esta função funciona tão bem entre planilhas quanto entre pastas de trabalho (mas você terá que habilitar links e estar atento às alterações em ambas as pastas de trabalho). Nenhum VBA é necessário, esta é uma função embutida simples.
Estendendo isso, você provavelmente receberá #N/Aerros para valores que não existem em sua tabela de origem. Envolva a fórmula =IFERROR( your vlookup() function , "" )para substituir quaisquer erros por uma célula em branco.
Responder2
A partir da sua pergunta e dos dados de amostra, é evidente que o que você está procurando como saída é uma coluna com dados correspondentes em um bloco contíguo de células, sem espaços em branco ou erros entre eles.
Se você deseja usar a abordagem de fórmula, isso pode ser alcançado usando funções INDEX & MATCH ligeiramente complexas em uma fórmula de matriz. Depois de ter os dados correspondentes em células contíguas em uma coluna, basta aplicar VLOOKUP para buscar o restante das duas colunas de sua tabela mestre na Planilha1.
Veja como. Abaixo estão duas capturas de tela da Planilha1 e Planilha2.
Sheet1 tem sua tabela mestre nas colunas A, B e C, Sheet2 tem sua lista para corresponder à coluna A da Sheet1
Agora na Planilha2, digamos, na célula C1, coloque a seguinte fórmula de matriz
{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}
Coloque esta fórmula sem os colchetes e, na barra de fórmulas, pressione CTRL + SHIFT + ENTER para criar uma fórmula de matriz e arraste-a para baixo até obter um erro nas células ou no comprimento da sua lista a ser correspondida. Agora todas as linhas acima das células de erro têm as células correspondentes da Coluna A da Planilha1.
Basta limpar as células de erro e você terá a lista em um bloco contíguo de células.
Agora em D1 insira uma fórmula VLOOKUP relativamente simples para buscar a próxima coluna correspondente da Planilha1
=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)
E em E1 coloque a fórmula
=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)
e arraste ambos para baixo. Você tem sua lista conforme desejado.
Você pode usar a opção Avaliar Fórmula integrada do Excel para avaliar a combinação INDEX MATCH para ter uma ideia de como isso funciona. Ele cria uma matriz de números de linha correspondentes e escolhe o primeiro, o segundo e o terceiro menor número à medida que você arrasta a fórmula para baixo.
Duas limitações aqui são que os dados devem ser retornados na ordem em que existem na Tabela Mestre e não como estão na tabela 'a ser correspondida' e caso você tenha mais de 1 célula correspondente, a primeira deverá ser retornada.