View original (French)

Formula for consistency control between two Excel sheets

BenhaB Posted messages 3 Status Member -  
 Benha -
Hello,

Your assistance would be welcome for this little practical case.

I have two sheets presenting the same tables in the same Excel file that need to display the same values. For various reasons, there is a risk that these values are entered incorrectly and that there is a misalignment between the rows of each column.

Therefore, I want to find a formula that allows me to check that the combination of fields in a row is the same on both sheets.

- 1) example where everything is fine:
SHEET 1
Product Quantity Price
X Y Z
SHEET 2
Product Quantity Price
X Y Z

- 2) example where there is an error:
SHEET 1
Product Quantity Price
X Y Z
SHEET 2
Product Quantity Price
K Y Z

The result of the formula should be presented in the form of an error message in the false field or any other form as long as I'm informed that the combination of values in the rows is not identical on both sheets.

I hope I have been clear enough, thank you in advance.

BenhaB

Configuration: Windows / Mozilla 11.0

3 answers

  1.
    PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello

    On sheet 2 in column D2
    put this formula
    =IF(A2<>Sheet1!A2,"error column A",IF(B2<>Sheet1!B2,"Error column B",IF(C2<>Sheet1!C2,"Error column C","Ok")))
    then drag it down
    If there are multiple errors, this formula will indicate the first error found on the line

    --
    It's by forging that one becomes a blacksmith. - It's at the foot of the wall that one sees the mason - one always learns from their mistakes
    0
    1. BenhaB Posted messages 3 Status Member
       
      Hello PHILOU10120,

      Thank you very much for the response, the formula works very well.
      Is it possible to take it a step further by asking Excel not only to check the consistency column by column but to verify by considering all the fields in the same row at the same time and comparing them to the same fields in the same row on the second sheet?

      Best regards,

      BenhaB
      0
  2. Raymond.PENTIER Posted messages 25 Status Member
     
    Hello.

    Another approach:
    =EXACT(A:A&B:B&C:C ; Sheet1!A:A&Sheet1!B:B&Sheet1!C:C)

    --
    It’s still the same Raymond PENTIER, member since 1997, contributor.
    0
    1. BenhaB Posted messages 3 Status Member
       
      Hi Raymond,

      Thank you very much for your response.

      I slightly modified the formula to suit my needs as I want to compare lines with each other:
      =EXACT(Sheet2!2:2, Sheet1!2:2)

      In this case, another issue arises: if I want to display the result of this formula on Sheet1, in column D for example, the reported result is "0" instead of "TRUE" or "FALSE". So I created Sheet3 where I inserted this formula.

      Is there a way to display the results of Sheet3 on Sheet1 without altering the formula on Sheet3? I thought about a color coding system to avoid alphanumeric values. (example:
      if inconsistency reported on Sheet1 -> "FALSE" on Sheet3 -> Red line on Sheet1.)

      Is this feasible?

      Best regards,
      BenhaB.
      0
  3. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello

    Try this column by column check

    =EXACT(Sheet1!A1, Sheet2!A1)*EXACT(Sheet1!B1, Sheet2!B1)*EXACT(Sheet1!C1, Sheet2!C1)

    or

    =EXACT(Sheet1!1:1, Sheet2!1:1)
    =EXACT(Sheet2!1:1, Sheet1!1:1)

    If the formulas are out of control range
    for example, if we check rows 1 to 5, the first formula will be in row 6 and the others
    otherwise circular calculation
    the formula being on the row being checked

    --
    It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from one's mistakes.
    0
    1. Benha
       
      Hello PHILOOU1020,

      Thank you for your response.
      I also found an alternative solution combining the concatenate and exact functions:
      - Sheet 1: concatenate the fields from the same row into a cell on sheet 4
      - Sheet 2: concatenate the fields from the corresponding row into a cell on sheet 4, thus creating a second column on sheet 4.
      - Apply exact to the two cells to check if the values in the two columns are identical.

      Best regards,
      Benha
      0