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

libreoffice-calcworksheet-function

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?

Best Answer

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

  • Related Question