array formula to check if ranges are identical

  • Thread starter Thread starter upstate_steve
  • Start date Start date
U

upstate_steve

Is there an array formula that will determine if the data in tw
multi-column ranges are identical?

Also, how would I return an array of the row numbers of the rows i
Range A that are not identical to the corresponding row in Range B?

I'm interested in a single array formula that will accomplish this, no
adding columns of formulas next to one of the ranges.

Thanks

Steve Przyborski
Boston, Mas
 
Is there an array formula that will determine if the data in two
multi-column ranges are identical?
Click to expand...

Identical just in terms of contents, e.g., {1;2;3} would be identical to
{3;1;2}, or indentical in terms of both contents and location? If the latter,
there's always the array formula

=AND(RngA=RngB)

If the former, the array formula

=AND((COUNTIF(RngA,"<"&RngA)=COUNTIF(RngB,"<"&RngA))
*(COUNTIF(RngA,"<"&RngB)=COUNTIF(RngB,"<"&RngB))
*COUNTIF(RngA,RngB)*COUNTIF(RngB,RngA))
Also, how would I return an array of the row numbers of the rows in
Range A that are not identical to the corresponding row in Range B?
Click to expand...

=SMALL(IF(RngA said:
I'm interested in a single array formula that will accomplish this, not
adding columns of formulas next to one of the ranges.
Click to expand...

Such single array formulas would have variable sizes depending on what your
ranges exactly contain. You'd need to wrap them inside IF constructs or live
with error values in some cells.
 
Harlan Grove:

Thanks. I'm still trying to tease out the internal logic of th
formulas, but they definitely work.

One more thing.

How about a formula that compares rows? That is to say, "true" if Rng
contains those rows--and only those rows--present in RngA (regardles
of sort order), but false otherwise, even if RngB contains all thos
values--and only those values--present in RngA.

Thanks

Stev
 
...
....
How about a formula that compares rows? That is to say, "true" if
RngB contains those rows--and only those rows--present in RngA
(regardless of sort order), but false otherwise, even if RngB
contains all those values--and only those values--present in RngA.
Click to expand...

I think you mean check if RngB spans exactly the same rows as RngA, so

=AND(ROWS(RngA)=ROWS(RngB),CELL("Row",RngA)=CELL("Row",RngB))
 
Back
Top