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?
Yes, this is possible. You need to select the XY (Scatter)chart type. If you start with this data table:
..., select it, start the chart wizard and select XY (Scatter), the result preview already shows that the data points are córrectly spaced:
(NB: i didn't customize the wizard's defaults yet - Calc did recognize the columns correctly).
The only problem is that you still can't tell Calc to use "one month" as scaling unit for the X axis. This means, the scale of the X axis will use days as scaling unit. If you set the major interval of the X axis to 30 (days), the resulting chart will look like this:
You're nearly right. You can adapt your formula slightly like this:
{=SUM(IF(A1:A5=B1:B5,1,0))}
Type this in without the curly {} brackets, and press Ctrl+Shift+Enter so that Excel knows it's an array formula. The curly brackets represent the fact it's an array formula.
Best Answer
Suppose
Jimmyis in cells A1 and B1. In cell C3 use functionEXACT, so the formula of cell C3 will be: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
TRUEif 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:The last cell shows
TRUEif and only if all cells in column C areTRUE.If there's a simpler method to compare two columns or two rows in Calc, don't hesitate to post it as an answer.