array formula to check if ranges are identical

[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

 

[Harlan Grove]

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

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?

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

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.

 

[upstate_steve]

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

 

[Harlan Grove]

...
....

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.

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))