Search code examples
excelexcel-formulacopycopy-pastelarge-data

How to ensure Excel field formulas are copied all the way down to a larger dataset

I have copied two different files to do a comparison. The first columns range from A-N. The second column range to compare with the first column range is P-AC. The O column is where my formula is and checks an exact comparison of fields. The following is my formula in the first row of O1:

=if(and(a1<>p1, b1<>q1, c1<>r1, d1<>s1, e1<>t1, f1<>u1, g1<>v1, h1<>w1, i1<>x1, j1<>y1, k1<>z1, l1<>aa1, m1<>ab1, n1<>ac1), "FAIL", "PASS")

The above formula is yielding incorrect results when I attempt to drag copy the formula down to the last row.

How do ensure the formula is incrementally copied all the way down?

I'm open to other suggestions on how to accomplish this in a more efficient manner.

EDIT TO INCLUDED SCREENSHOT OF EXPECTED VALUES:

Rows A3-N3 (includes column header): enter image description here

Rows P3-AC3 (includes column header): enter image description here

Rows 01-O3 (column header should be "PASS" as is because all the column headers match from both documents):

enter image description here

To reiterate, my result column in O for the screenshots shown should be "PASS" as they are, but when I copy the formula all the way down it starts to get funky. They are showing "PASS" when should be showing "FAIL".

Solution

  • I'm checking for all columns between the docs. They should all be the same and I know that some aren't which is what I'm checking for.

    Your logic is off. Use:

    =if(and(a1=p1, b1=q1, c1=r1, d1=s1, e1=t1, f1=u1, g1=v1, h1=w1, i1=x1, j1=y1, k1=z1, l1=aa1, m1=ab1, n1=ac1), "PASS", "FAIL")

    or

    =if(or(a1<>p1, b1<>q1, c1<>r1, d1<>s1, e1<>t1, f1<>u1, g1<>v1, h1<>w1, i1<>x1, j1<>y1, k1<>z1, l1<>aa1, m1<>ab1, n1<>ac1), "FAIL", "PASS")

    If you try to use AND instead of OR in the second formula, then the only way you'll get "FAIL" is if every single condition is TRUE, i.e. a1 must be different than p1, b1 must be different than q1, and so on.