Comparing arrays

[JoshuaSQ]

Is there any way to compare the contents of two arrays?

This is to verify data entry... in certain cases records will be double keyed to check for accuracy. I would like a quick easy way to check the similarity of two sets of data.

For instance, in the example below i would like a formula that returns "true" since the data is exactly the same.

Record1 4 5 6 4 1
Record1 4 5 6 4 1


Is there anyway to do this?

Thanks!

[JoshuaSQ]

Nevermind, I found what I was looking for.

=SUM(IF(B1:P1=B2:P2,0,1))

Thanks.

[JBeaucaire]

One really brute force way to check... of those cells are A1:F1 and A2:F2, then in another cell:

=(A1&"-"&B1&"-"&C1&"-"&D1&"-"&E1&"-"&F1) = (A2&"-"&B2&"-"&C2&"-"&D2&"-"&E2&"-"&F2)

[shg]

=and(a1:f1=a2:f2), array-entered.

[JBeaucaire]

=and(a1:f1=a2:f2), array-entered.

Ooh, I was hoping! But I can't get that to work.

[shg]

Array formula, JB.

[JBeaucaire]

Yeah, I saw that. tried it both ways, of course, #VALUE error both times.

[shg]

Post an example that doesn't work, JB.

[JBeaucaire]

(*blush*) went to post it, same exact sheet...now it's working. *sigh*

I'm glad though. I have many uses for that new nugget. Thanks.