Unindo duas colunas para criar uma lista de dados

Question

Atualmente, tenho duas colunas que precisam ser comparadas. Col A, Folha 1 & 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

Só preciso exibir os resultados que contêm o mesmo número da Folha 2. Existe uma maneira melhor de 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

    

Answer 1

Esta é a função principal de =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 em C1 você colocaria =VLOOKUP(A1,Sheet1!A:C,3,False)

Essa função funciona entre folhas de trabalho, assim como entre pastas de trabalho (mas você terá que habilitar links e estar atento a alterações em ambas as pastas de trabalho). Nenhum VBA é necessário, esta é uma função inline simples.

Estendendo-se sobre isso, você provavelmente receberá #N/A erros para valores que não existem na sua tabela de origem. Embrulhe a fórmula em =IFERROR( your vlookup() function , "" ) para substituir qualquer erro com uma célula em branco.

    

Answer 2

De sua pergunta e amostra de dados, é evidente que o que você está procurando como uma saída é uma coluna com dados correspondentes em um bloco contíguo de células sem quaisquer espaços em branco ou erros entre eles.

Se você deseja usar a abordagem de fórmula, ela pode ser obtida usando um INDEX & MATCH funções 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 da tabela mestre em Sheet1.

Veja como. Abaixo estão dois screenshots de Sheet1 & Folha2.

Folha1temsuatabelamestrenasColunasA,B&C,Sheet2temsualistaparasercorrespondidacomacolunaAdaplanilha1

Agora,naPlanilha2,nacélulaC1,coloqueaseguintefórmuladematriz

{=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 as chaves 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 tamanho 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.

Limpe as células de erro e você terá a lista no contigente bloco 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 os dois para baixo. Você tem sua lista conforme desejado.

Você pode usar a opção Avaliar Fórmula incorporada 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, depois o segundo e, em seguida, o terceiro menor número, à medida que você arrasta a fórmula.

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élulas correspondentes, a primeira deve ser retornou.