Procurando retornar algum valor com base em quantas vezes um valor corresponde em duas colunas

Question

Eu tenho números de identificação associados a até dois valores (SYS-A e SYS-B) em outra coluna, mas cada ID pode aparecer várias vezes em SYS-A e / ou SYS-B. Eu preciso do Excel para olhar para cada ID e me diga se ele está associado a ambos os sistemas ou se está associado a apenas um e qual sistema, se apenas um. Posso ter a coluna "RESULTADO" em outra guia, pois tenho uma guia com pagamentos por social e outra com o sistema ao qual cada ID está associado? "Resultado" deve ser dependente da tabulação 1.

TAB 1
ID ------ SYS
1 -------- Um
1 -------- Um
1 -------- B
1 -------- Um
2 -------- B
3 -------- Um
3 -------- A

TAB 2
ID ----- PAGAMENTO --- RESULTADO
1 --------- 5 --------------- BOTH
2 --------- 3 ----------------- B
3 --------- 2 ----------------- A

    

Answer 1

Ideal:

1. Adiciona uma coluna como chave a ser pesquisada, a chave é a concatenação de ID e SYS;
2. Usa countif para verificar a existência de ID + "A" e ID + "B", coloca o resultado em bitmap - bit0 indica existência de B, bit1 indica existência de A, resultando no seguinte mapa: 0 - Nenhum, 1 - B, 2 - A, 3 - Ambos;
3. Usa lookup para traduzir o valor 0, 1, 2, 3 em texto esperado de acordo com o mapeamento acima;

Etapas:

Suponha que a Tabela 1 ID 1 esteja em A2, Tabela 2 ID 1 esteja na célula A11:

1. Na Tabela 1, a fórmula da célula C2 é =A2&B2 e, em seguida, preencha;
2. Na Tabela 2, a fórmula de C11 é =(COUNTIF($C$2:$C$8,A11&"A")<>0)*2+(COUNTIF($C$2:$C$8,A11&"B")<>0) e, em seguida, preenche-a;
3. Na Tabela 2, a fórmula de D11 é =LOOKUP(C11,{0,1,2,3},{"NONE","B","A","BOTH"}) e, em seguida, preenche-a;

Exemplo:

ID  SYS KEY
1   A   1A
1   A   1A
1   B   1B
1   A   1A
2   B   2B
3   A   3A
3   A   3A

ID  PAYMENT val RESULT
1   n/a     3   BOTH
2   n/a     1   B
3   n/a     2   A

    

Answer 2

Uma possível solução com base no SUMPRODUCT, IF & AND funciona com uma coluna auxiliar

FormulanacélulaH4eparaserarrastadaparabaixo.

=IF(AND(SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="A"),$D$4:$D$13)>=1,SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="B"),$D$4:$D$13)>=1),"BOTH",IF(SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="A"),$D$4:$D$13)>=1,"A",IF(SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="B"),$D$4:$D$13)>=1,"B","Invalid")))

    

Answer 3

Use esta fórmula em uma nova guia =IF(SUMPRODUCT(('TAB 1'!$A$2:$A$8='TAB 3'!A2)*('TAB 1'!$B$2:$B$8="A"))=COUNTIF('TAB 1'!$A$2:$A$8,'TAB 3'!A2),"A",IF(SUMPRODUCT(('TAB 1'!$A$2:$A$8='TAB 3'!A2)*('TAB 1'!$B$2:$B$8="B"))=COUNTIF('TAB 1'!$A$2:$A$8,'TAB 3'!A2),"B","BOTH"))

Na TAB 1 você tem seus dados sem passar de A2 para B8, por exemplo Na TAB 3 você pode copiar e colar todo o ID e depois remover duplicatas iniciando os valores de ID de A2 e em B2 colar a fórmula acima