/

Compare Two Columns in LibreOffice Calc – Steps and Examples

11939 views
3

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.

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.

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
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
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
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
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.

Compare two columns in LibreOffice and showing the differences
Conditional formatting – column differences

And you will see the values that are different showing in RED colour.

Conclusion

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.

Cheers.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Join our Telegram channel and stay informed on the move.


arindam

Creator of libreofficehelp.com. The aim is to help as many people with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
BARACK BALO

Irish work

Mogens

In my LibreOffice Version on Ubuntu i had to use a semikolon instead of a comma in the COUNTIF condition.

3
0
Would love your thoughts, please comment.x