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.
EXACT("Text1"; "Text2")
Text1 refers to the first text to compare.
Text2 is the second text to compare.
Examples
=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.
=EXACT(A2,B2)
Compare two columns using EXACT function in Calc
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.
Result after comparing two columns using EXACT function in Calc
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.
data for mismatch highlight
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.
=MATCH(A1,$B$1:$B$6,0)
MATCH function showing the differences
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.
Conditional formatting – column differences
And you will see the values that are different showing in RED colour.
