This guide explains how to compare two columns of texts in the LibreOffice Calc spreadsheet programme.
LibreOffice Calc offers several methods to help you compare two lists or two columns and find out their differences. You can try the methods explained below to find out the mismatched items.
Table of Contents
Compare two columns in LibreOffice Calc
Using EXACT Formula Method
The easiest way to compare is to use the EXACT function. The EXACT function takes two arguments, compares them, and returns TRUE or FALSE based on whether they match.
Text1 refers to the first text to compare.
Text2 is the second text to compare.
=EXACT("libreoffice";"Libreoffice") returns FALSE
=EXACT("libreoffice";"libreoffice") returns TRUE
You can apply this function to a cell or an entire column to compare texts in cells in those columns. Let’s take a look. We have the following test data, which has two columns. Not all the rows are identical; there are differences.
Let’s create a helper column C. In this column, in the first data row, enter the EXACT function.
And hit enter. The result is TRUE for the above test data. Now select the cell handle and drag it to the bottom. Or, double click on the cell handle to autofill.
You can see the result column filled with TRUE or FALSE.
Those cells are identical, they are shown as TRUE, and the rest are false.
Now you can use the
Menu > Data > Auto Filter or press CTRL+SHIFT+L to filter out the relevant fields based on TRUE or FALSE.
Using MATCH function
The above method only checks the mismatched values in two parallel rows. But it didn’t give you the cells which are entirely missing in another column.
For example, we have these simple two columns with data.
As you can see, the above EXACT method will not work if you want to see the data that are not present in column B. The value Mars and Venus is not present in column B, but how you can find that out.
So, there are various ways to do it. You can use the MATCH function or conditional formatting to view the differences.
Let’s add a helper column C with the below function to demonstrate.
So, as you can see, Mars and Venus are not present in column B and its shown as #N/A in column C.
Using Conditional Formatting
Furthermore, there is a way to highlight this via conditional formatting because it becomes easier to analyse data visually.
So, select col A and Col B. Then add the name Mylist1 and Mylist2 as named ranges, as shown below.
Then click on A1 and choose
Menu > Format > Conditional > Condition. Choose the condition as “
Formula is“, then apply style as
BAD on the formula type. And add the Range as
A1:A6. Press ok.
And you will see the values that are different showing in RED colour.
Finally, you can easily compare two columns and see the differences using the above methods. I hope this article gives you some idea of how you can work out your use cases. Do let me know in the comment section down below whether it helps.
In my LibreOffice Version on Ubuntu i had to use a semikolon instead of a comma in the COUNTIF condition.
Yes. Use semicolon. Its based on the locale. USA locale uses comma.