Compare as colunas de diferentes arquivos Excel e, se houver correspondência, anexe à última coluna (na mesma linha) um status

Question

Quero começar dizendo que não sou especialista em Excel, portanto, preciso de ajuda.

Vamos supor que eu tenha 3 arquivos do Excel: main.xlsx , 1.xlsx e 2.xlsx . Em todos eles tenho uma coluna chamada Serial Numbers . Eu tenho que:

• pesquise todos números de série em 1.xlsx e 2.xlsx e verifique se eles estão em main.xlsx .

Se um número de série for encontrado:

• na última coluna de main.xlsx , na mesma linha com o número de série encontrado write OK + name_of_the_file_in which_it_was_found . Senão, escreva NOK . Ao mesmo tempo, escreva em 1.xlsx e 2.xlsx ok ou nok na última coluna se o número de série foi encontrado ou não.

Menção : serial number pode estar em colunas diferentes em 1.xlsx e 2.xlsx

Exemplo:

main.xlsx

name date serial number phone status
a      b      abcd        c         <-- ok,2.xlsx
b      c      1234        d         <-- ok,1.xlsx
c      d      3456        e         <-- ok,1.xlsx
d      e      4567        f         <-- NOK
e      f                  g         <-- skip,don't write anything to status column

1.xlsx

name date serial number phone status
a      b      1234        c          <-- OK (because is find in main)
b      c      lala        d          <-- NOK (because not find in main)
c      d      3456        e          <-- OK (because find main)
d      e      jjjj        f          <-- NOK (because not find in main)
e      f                  g          <-- skip,don't write anything to status column

2.xlsx

name date serial number phone status
a      b                  c          <-- skip,don't write anything to status column
b      c      abcd        d          <-- OK (because find main)
c      d      4533        e          <-- NOK (because not find in main)
d      e      jjjj        f          <-- NOK (because not find in main)
e      f                  g          <-- skip,don't write anything to status column

Eu sei que posso usar VLOOKUP ou MATCH , mas não estou familiarizado com as fórmulas do Excel.

    

Answer 1

Usaremos uma combinação das seguintes funções:

• ISBLANK para verificar se uma célula tem um valor, senão vamos ignorá-lo e o status resultante estará vazio.
• VLOOKUP para encontrar um valor em outra tabela em outro arquivo. Poderíamos ter usado a função LOOKUP , mas ela tem seus problemas e VLOOKUP nos permite especificar que queremos uma correspondência exata.
• ISERROR para verificar se VLOOKUP encontrou uma correspondência ou retornou um erro.

Na coluna de status de main.xlsx , use a seguinte fórmula na primeira linha e aplique ao restante das linhas abaixo dela:

=IF(ISBLANK(C2),"",IF(ISERROR(VLOOKUP(C2,[1.xlsx]Sheet1!$C:$C,1,FALSE)),IF(ISERROR(VLOOKUP(C2,[2.xlsx]Sheet1!$C:$C,1,FALSE)),"NOK","ok,2.xslx"),"ok,1.xslx"))

Na coluna de status de 1.xslx e 2.xlsx use esta fórmula na primeira linha e aplique ao restante das linhas abaixo dela:

=IF(ISBLANK(C2),"",IF(ISERROR(VLOOKUP(C2,[Main.xlsx]Sheet1!$C:$C,1,FALSE)),"NOK","OK"))

Nota: As fórmulas referem-se à coluna C porque, com base nos exemplos que você forneceu, o número de série está localizado lá.