Como reorganizar 2 colunas de dados com duplicatas em uma coluna

Question

Desculpe se o título é confuso. Eu tenho ficado acordado quase a noite inteira tentando resolver esse problema e não sei como dizer isso. Qualquer ajuda seria apreciada.

Eu tenho uma planilha com nomes na coluna A e números de identificação na coluna B. Parece que isso

Sally     1004
Sally     1005
Sally     1006
Robert    1007
Robert    1008

ect.

Eu tenho outra planilha que, devido a algumas circunstâncias, não posso legalmente filtrar de qualquer forma que tenha nomes como esse

Sally
Sally
Robert
Robert
Robert

Eles são todos alfabéticos, mas como você pode ver no caso de Sally, ela aparece mais vezes em primeira instância do que na segunda folha, que é completamente boa. No entanto, Robert aparece mais vezes na segunda folha do que na primeira e eu preciso disso para retornar algum tipo de erro. Eu também preciso de Sheet2 para ter números de ID para que eles apareçam. Este é o objetivo final

Sally     1004
Sally     1005
Robert    1007
Robert    1008
Robert    #N/A           (or any other error)

Passei horas tentando encontrar uma fórmula de matriz para funcionar e não obtive sucesso. Se você tem alguma idéia de como devo proceder, eu agradeceria muito.

    

Answer 1

Use esta fórmula:

=INDEX(Table1[ID],SMALL(IF(A2=Table1[name],ROW(Table1[name])-ROW(Table1[[#Headers],[name]]),10^9),COUNTIF(Sheet2!$A$1:A2,A2)))

Claro que esta é também uma fórmula de matriz.

• %código%
  • retorna o número da linha para as linhas na tabela de origem que contém o nome real
    (corrigido pelo número da linha do cabeçalho da tabela, para funcionar corretamente na função de índice)
  • devolve IF(A2=Table1[name],ROW(Table1[name])-ROW(Table1[[#Headers],[name]]),10^9) para os outros (só para obter um grande número que não confunda a função 10^9 , talvez SMALL funcione também)
• "" - conta o número de nomes acima da linha atual
• COUNTIF(Sheet2!$A$1:A2,A2) - retorna o menor número de linha (corrigido)
• SMALL(IF(...),10^9),COUNTIF(...)) - retorna o ID

Para melhor legibilidade da fórmula que converti seu primeiro intervalo em uma tabela, é claro que você também pode usar endereços.