How to find if 2 columns (or rows) in LibreOffice Calc are identical?

Question

I have two long columns of data in LibreOffice Calc, something like below:

Jimmy  | Jimmy
Bunk   | Bunk
Lester | Lester
Prez   | Prez
...

These columns are most likely identical, that is, they contain exactly the same entries. However it is possible that for some reason they are not, for example if one of the columns was re-ordered. I want to compare them to see if they are indeed identical.

How do I compare two columns (or two rows) in Calc and see if they are exactly the same?

Answer 1

Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be:

=EXACT(A1,B1)

Apply the same formula for every cell in column C. In Calc you can grab the little black square in the cell and drag it, so the same formula is applied for multiple cells, but the cells of column A and B are changed accordingly.

Column C now shows TRUE if and only if cells in corresponding columns to the left are both identical.

Then for every value of column C apply function AND. For example if there are 100 entries in column C, your cell would look like:

=AND(C1:C100)

The last cell shows TRUE if and only if all cells in column C are TRUE.

If there's a simpler method to compare two columns or two rows in Calc, don't hesitate to post it as an answer.

Answer 2

LibreOffice Base can compare lists more effectively. However some setup is required. Try these steps:

1. Insert a new row in Calc. Set contents of cell A1 to C1 and cell A2 to C2.
2. File -> New -> Database. Connect to an existing database, selecting Spreadsheet from the list.
3. Press Next>> and specify the location of the spreadsheet. Press Finish and save the database.
4. Under Tables, "Sheet1" should now appear (or whatever your sheet is named). When you double-click it, the data should be displayed.
5. Go to Queries and choose Create Query in SQL View. Enter the following query:

SELECT C1,C2 FROM "Sheet1" WHERE C1 <> C2

Now save and view the query. It will display all rows which are different.

To reduce this to a single value, use COUNT(*) instead.