[Solved] Function to test if 2 ranges are element-wise equal

[smjg]

Hello,

Using OpenOffice 4.1.5, Windows 10.

It ought to be possible to write a formula to compare two ranges and test whether they contain the same values, without having to list them individually.

In other words, something like

Code: Select all

=EQUAL(A1:A4;B1:B4)

which would be equivalent to

Code: Select all

=AND(A1=B1;A2=B2;A3=B3;A4=B4)

but the ranges could potentially be much bigger than this, such that listing all the pairs of cells to compare would be too cumbersome.

Can anyone advise?

Thanks

[FJCC]

This formula will tell you how many pairs in A1:A4 vs B1:B4 are unequal, so a value of zero means they are all equal.

Code: Select all

=SUMPRODUCT(A1:A4 <> B1:B4)

[MrProgrammer]

It ought to be possible to write a formula to compare two ranges and test whether they contain the same values, without having to list them individually.

Type =AND(X2:X999=Y2:Y999) and press ⇪⌘Enter (Shift+Command+Enter) on a Mac or Ctrl+Shift+Enter on other platforms; if you press Enter by mistake, use Edit → Delete contents → Formulas, then try again.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[smjg]

Thanks both. I hadn't got my head around how array formulae work in OpenOffice. I'd wondered why SUMPRODUCT works but neither SUM nor PRODUCT does, but then discovered that SUMPRODUCT is the only one of the three that has "forced array handling".

[RusselB]

Please note that for any of the responses, the range sizes must be the same... X columns and Y rows

Off topic, please go to the User Control Panel and update your forum signature.