How to Compare More Than One Data Between Two Sheets and Add That Data Based on One Condition

Question

How to Compare More Than One Data Between Two Sheets and Add That Data Based on One Condition

Navigation

#1 by (3 votes)

-1

I have two spreadsheets, they have basically the same data, but they are not 100% equal.

I need to get the client's Id (column A) on worksheet1 and check on the line where it exists on worksheet2 (column A), if the Item (column k) of that client on worksheet1 is equal to Item (column C) on worksheet2 .

I need to add only the cases where in column 1 the column L is equal to the text "success".

Sheet1

A - Id | ... | K - Item | L - STATUS |
   123 | ... |  1234-5  |  Sucesso   | <- Mudou Item
   124 | ... |  1200-0  |  Sucesso   | <- Não mudou Item.
   987 | ... |  0503-7  |  Falha     |

Sheet2

A - Id | ... | C - Item |
  123  | ... |  1234-0  | <- Item Original
  124  | ... |  1200-0  | <- Item Original
  987  | ... |  0503-7  |

Expected result:

Sum: 1.

OBS:

This summation goes in another resultsheet.
The two spreadsheets have the same Id's, arranged in the same order.

I can not see a way to do this, due to the complexity of checking two worksheets, two values, and summing based on one condition.

excel

asked by anonymous 05.05.2017 / 15:55

1 answer

Function inputmask () for cpf and cnpj PHP does not execute OnClick return confirm

score 3 · Accepted Answer

One way to solve this is to create a formula in your worksheet 1 (or even in 2) that returns 1 or 0, and you can do a sum in your third worksheet, see the formula:

=SE(E(L2="sucesso"; PROCV(A2;Plan2!$A$2:$C$7;3,1)=K2);1;0)

Breaking the function
Remember, the function will run on each line, so understand that the reference L2 , A2 , and K2 refer to your first item.

=SE(
    E(
      L2="sucesso"; --> só vai fazer o procv se for sucesso
      PROCV( --> faz uma busca num intervalo
            A2; --> o valor que está sendo procurado (seu primeiro ID)
            Plan2!$A$2:$C$7; --> é a tabela completa na planilha 2
            3, --> o procv vai retornar o valor da 3º coluna da tabela na plan2
            1 --> a correspondência precisa ser exata
           )=K2); --> se o valor que encontrou na plan2 é igual ao item da plan1
    1; --> coloca o valor 1 na nova coluna "J"
    0) --> coloca o valor 0

After all this, you can make the sum in this new "J" column. Any new condition, just add a new parameter in the function " E ".