Comparing text lists with conditional formatting in OOo/LO Calc

· Given that I hadn't found enough completely satisfying information myself, I had to make a separate blog post about it, as an acquaintance asked me for how to solve list issues.
· I deem my explanation better than others', as I think this one explains the relationships much better (:smug:).
· Applies to both OpenOffice.org Calc and LibreOffice Calc.

This post actually assumes the following:
• That you already have two lists of textual information that you want to compare;
• That you've already created your own custom styles for formatting the necessary cells, but that it's hard to get one's head around about how to do all that comparing.

So, then:

• Select the column range that you want to format (for example A1:A15);
• From the menu bar choose Format > Conditional Formatting;
• In its window, "Condition 1" is selected by default; below choose "Formula is", and in the next field it gets important,

  where inside COUNTIF brackets you specify the target column (range array) you want to compare the selected column against:

  So, you have A1:A15 selected (which eventually gets formatted) and you want to compare it against B1:B15;

  the latter you have to write in array format, with a dollar $ sign before every column letter and row number in the range ($B$1:$B$15;  );

  After the semicolon, specify the last cell of the selected range (the one you want to format, not the target range), which is A15. The whole formula line goes almost exactly like this:
  
  COUNTIF($B$1:$B$15;A15)=1

=1 means that you apply the format only if there is a match;
  =0 is inverse: most cells are formatted, those that match are not.
  
• Don't forget to specify your custom formatting style for cells, then click OK.

All this should work in older OO.o versions, too.

Source: Conditional Formatting: Comparing two Lists (has screenshots), archived from www.openofficetips.com/2006/02/01/conditional-formatting-comparing-two-lists/