Excel Formula Assist - Encontre os números que faltam

Question

Eu tenho uma planilha exportada do meu banco de dados que contém Role s e Account ID s. Existem 4-5 nomes de funções por ID de conta. Eu preciso de uma fórmula para exibir todas as contas que não têm uma função de RBD . Alguém pode ajudar?

Exemplo:

Account ID   Account Name       Team        Role
1            123 AG SERVICE     National    MANG
1            123 SERVICE        National    CAP
1            123 AG SERVICE     National    RGL
1            123 AG SERVICE     National    CS
1            123 AG SERVICE     National    XSM
1            123 AG SERVICE     National    RBD
1            123 AG SERVICE     National    Q4
2            NORTHEAST INC      National    MANG
2            NORTHEAST INC      National    CAP
2            NORTHEAST INC      National    RGL
2            NORTHEAST INC      National    CS
2            NORTHEAST INC      National    XSM

Eu gostaria que a consulta exibisse 2 como o número da conta que não tem o papel de RBD .

Qualquer ajuda é apreciada!

    

Answer 1

SumProduct é seu amigo aqui. SumProduct permite que você realize vários testes em uma série de dados, linha por linha.

Em uma guia separada, liste seus IDs de conta distintos na coluna A. Na coluna B, teremos uma contagem de todas as linhas de cada ID da conta, independentemente de uma fórmula de contagem ... como =countif(Sheet1!A:A, Sheet2!A1) , supondo que Folha1 tenha sua dados, e estamos trabalhando em Sheet2.

Na coluna C, vamos implantar o sumproduct. Vamos querer retornar a contagem de linhas para cada accountID em que a função não é igual a RBD. Isso será parecido com =SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1) . O que isso está fazendo é testar cada uma das condições em parantheses para true / false para cada linha no intervalo. Então está adicionando todas as linhas onde ambas as condições eram verdadeiras. Nossas duas condições aqui são que a coluna A na planilha1 tem o ID da conta em que estamos interessados e que a coluna D na planilha1 não contém RBD.

Na Coluna D da sua segunda guia, subtraia Colunas C da Coluna B: =B1-C1 . Se isso não for 0, o ID da conta não terá RBD.

Isso tudo pode ser escrito em uma única fórmula, em vez de ser espalhado nas colunas B, C e D, como =if(countif(Sheet1!A:A, Sheet2!A1)-SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1) = 0, "", "Missing RBD!") , se você estiver em toda a brevidade.

Você pode, no final, ser melhor fazer isso com o SQL no seu banco de dados. Toda vez que você usa o sumproduct (ou precisa dele), é melhor usar o SQL, já que é mais uma função do tipo banco de dados. Algo como

SELECT distinct_account_ids.Account_ID FROM (SELECT DISTINCT ACCOUNT_ID FROM <table>) as distinct_account_ids LEFT OUTER JOIN (SELECT Account_ID FROM <table> WHERE ROLES = 'RBD' GROUP BY Account_ID ) as ids_with_rbd ON distinct_account_ids.Account_id = ids_with_rbd.Account_id WHERE ids_with_rbd.Account_id is null;