How to Compare More than One Piece of Data Between Two Sheets and Sum That Data Based on a Condition

Asked

Viewed 807 times

-1

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

I need to take the Id(column A) of the client in worksheet1 and check in the row where it exists in worksheet2(column A), if the Item(column k) of that client in worksheet1 is equal to the Item(column C) in worksheet2.

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

Planilla1

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

Planilla2

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

Expected Result:

Sum: 1.

OBS:

  • This sum goes in another result sheet.
  • Both worksheets 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 spreadsheets, two values and making the summation based on a condition.

excel

  • You want an automatic process (a macro for example) or the steps how to do this in your spreadsheet?

  • I would like the steps of how to perform this to learn better.

1 answer

3


One way to solve it is to create a formula in your worksheet 1 (or even in 2) that returns 1 or 0, and with this, you can make 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)

Dismembering the function
Remember, the function will be executed on each line, so understand that the reference L2, A2 and K2 refer to its 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 carry out the sum in this new column "J".
Any new condition, just add a new parameter to the function "E".

  • I gave a small addition of more content, to make more clear what I need.

  • Isn’t there a way that you don’t have to create a new column in these tables and that result is counted by row by row ? The biggest challenge, is really to make it go for example A1:A1000 without having to do the manual increment.

  • It is perfectly possible to create a function (VBA) that does all this process automatically (you would only need to feed it stating which and each column), so have a time I will try to do this function (if no one else does it).

  • It happened "#N/D", I’m trying to see why.

  • Once the function is created, the only cost will be to correct the range of the table in plan2 (in the first formula). Then the filler handle will do all the rest.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.