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

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?

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.

• Instead of `EXACT` you can simply enter `=A1=B1`. One possibility similar to your solution is to use an Array Formula, although I do not necessarily recommend it. Mar 8 '16 at 2:16

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.