Questão Excel Vlookup para 2º, 3º, 4º ou 5º valor de texto, em vez do 1º valor correspondente


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

Então, para ID 0123 eles só têm um, só haveria um valor para recuperar, mas para ID 456, Eu precisaria da planilha do Excel para ter os valores de Certificação na mesma linha para o ID, mas em todas as vezes que o número de certificações houver:

ID    Certif1        Certif2
456    CPR           Safety 

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


1


origem


possível duplicata de O Excel 2007 transpõe / combina várias linhas em uma e Como combinar valores de várias linhas em uma única linha no Excel?; Veja também Excel 2010 Mover dados de várias colunas / linhas para uma única linha e talvez outros. - G-Man
Estou confuso - por que sua linha de saída para 456 não tem uma coluna para Enfermagem? - Mike Honey
Deve ter enfermagem, um descuido da minha parte. K - Kelly


Respostas:


Você pode construir a tabela transformada em duas etapas.

Etapa 1: gere 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. Verifica a Unique Records Only caixa de seleção e escolha Copy to another location. Defina o local para a saída e clique em OK. Isso fornecerá a lista de IDs 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 o Certif1 coluna, 2 para Certif2 coluna (você terá que ajustar o termo de subtração para corresponder aos seus dados).

Uma vez inserido, selecione a célula, clique dentro da barra de fórmulas e pressione Ctrl+Mudança+Entrar. Isto irá 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 com quantas colunas forem necessárias (apenas 3 para seus dados de amostra desde 456 tem 3 certificações). Isso deve dar o que você quer.

Amostra: 

enter image description here


1



Usar COLUMNS ($ A: A) em vez de COLUMN () - 6 na primeira célula significa que nenhum ajuste manual é necessário, onde quer que o OP decida colocar essa fórmula. O mesmo se aplica a ROWS ($ 1: 1) no lugar de construções com ROW (). - XOR LX
Essa fórmula funcionou, muito obrigada !!!! ;-) - Kelly


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

enter image description here

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

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.


0



Estou tentando evitar listar cada certificação em colunas para todos os funcionários, pois nem todos os funcionários os possuem. Eu modifiquei minha tabela dinâmica para tentar opções, mas não obtive o resultado que estou procurando. Obrigado. - Kelly