Excel Vlookup para o segundo, terceiro, quarto ou quinto valor do texto, em vez do primeiro valor correspondente

Question

Eu tenho linhas de possíveis IDs de IDs de funcionários duplicados, com outra coluna de valores diferentes para a mesma pessoa. Eu preciso de uma única coluna de IDs únicos com as certificações transpostas na mesma linha. Por exemplo:

ID       Certification
0123     CPR
456      CPR
456      Nursing
456      Safety
789      Engineering
966      CPR
966      Safety

Portanto, para o ID 0123 eles têm apenas um, só haveria um valor para recuperar, mas para ID 456 , eu precisaria que a planilha do Excel tivesse os valores de Certificação na mesma linha para o ID, mas tantas vezes quantas o número de certificações:

ID    Certif1        Certif2
456    CPR           Safety 

Qualquer ideia seria apreciada, pois os valores são todos texto. Eu também estou usando o Excel 10.

    

Answer 1

Você pode criar a tabela transformada em duas etapas.

Etapa 1: gerar uma lista de IDs exclusivos.

Você pode fazer isso com a ferramenta Filtro avançado. Selecione a coluna de IDs incluindo o cabeçalho e clique em Filtro Avançado na faixa de opções Dados. Verifique a caixa de seleção Unique Records Only e escolha Copy to another location . Defina o local para a saída e clique em OK. Isso fornecerá a lista de códigos exclusivos para as linhas da sua nova tabela.

Etapa 2: obtenha certificações que correspondam a cada ID em novas colunas.

Você pode usar uma fórmula de matriz para retornar as certificações correspondentes às colunas apropriadas. Na linha do primeiro registro em sua nova tabela, insira a fórmula abaixo, ajustada para corresponder à sua planilha.

=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$F2,ROW($B$1:$B$8),1000000),COLUMN()-6)),"")

onde A1:B8 são os dados originais que você forneceu, F2 é o ID na nova tabela para a qual você está procurando certificações e COLUMN()-6 é igual a 1 para a coluna Certif1 , 2 para coluna Certif2 (você precisará ajustar o termo de subtração para corresponder aos seus dados).

Uma vez introduzido, selecione a célula, clique dentro da barra de fórmulas e pressione Ctrl + Deslocar + Enter . Isso inserirá a fórmula como uma fórmula de matriz.

Depois de fazer isso, preencha a fórmula abaixo da coluna. Em seguida, preencha-o para quantas colunas forem necessárias (apenas 3 para seus dados de amostra, pois 456 tem 3 certificações). Isso deve dar o que você quer.

Amostra:

    

Answer 2

Você pode usar uma tabela dinâmica para criar uma estrutura tabular, embora um pouco diferente do layout que descreve.

Para o exemplo à esquerda, arraste ID e Certificação para a área da linha e selecione um layout tabular. Remova todos os totais e subtotais.

Para o exemplo à esquerda, arraste ID para a linha e Certificação para a área da coluna e Certificação novamente para a área Valores.