Calc: How to Locate Unique Records in Two or More Columns?

[jdanniel]

Hello, everyone. I have a question about finding unique (not distinct) records.

I’ve read several web sites, and viewed a few videos, that demonstrate how to find unique records in a column.

However, I want to find unique records in two or more columns.

For instance, in the test spreadsheet I created, Column A’s header is “Original.” Column B’s header is “Updated.”

I’d like to find all of the unique records in both columns.

For example, in Column A, Z is a unique record. It appears in the spreadsheet only once.

In Column B, the numbers 11-15 and letters A-C are unique. All of the other records appear more than once.

In Excel, I can do this with conditional formatting. What is the easiest way to do this with Calc?

Thank you! J. Danniel (Please feel free to call me Jd)

[Wanderer]

@jdanniel

In Excel, I can do this with conditional formatting.

So “find” is meant as “highlight”…
You may use also conditional formatting, and use a COUNTIF, to check, if the current item can only be counted once.

[jdanniel]

Calc’s Conditional Formatting is quite different from Excel’s. Excel lets you choose unique or duplicate values. I don’t see that in Calc. Where do you apply the COUNTIF option?

[schiavinatto]

https://extensions.openoffice.org/en/projectrelease/único-unique-010

It doesn’t solve completely, but it facilitates

Do it by columns, join the results in a single column and redo.

[Wanderer]

@jdanniel

Where do you apply the COUNTIF option?

Let’s assume you have A12 as active cell (I like to start, with a cell, where the condition is met) and use “conditional format” from context menu or menu format.
.
Realise there is a menu on the condition, where you can change to “formula”. Now enter a formula (no = necessary, wich has a true/false result) like the (not tested) following one:

COUNTIF(A12; $A$2:$A$20)=1

Select a predefined style for the cell or create a new one.
.
If in doubt, test your condition now, or change the range to something like A2:A20 The condition will be adapted, as usual, therefore the $ in the formula.
.
Test, then extend the condition to include other columns etc.

[LeroyG]

@Wanderer

A12; $A$2:$A$20

$A$2:$A$20; A12

Version: 7.4.7.2 (x86) / OS: Windows 6.1

Using the questioner’s range of data: COUNTIF($A$2:$B$19;A2)=1

EDIT: There was an error in my screeshot: instead of $B$20 it should be $B$19.

[jdanniel]

Thank you, everyone, for your assistance.

[JohnSUN]

In addition, you can use the built-in condition “is not duplicate”

Additional condition 1 will allow you not to color empty cells