[Solved] Count unique values in a list (line/row)

[humulus]

Hi
is there a Calc function (or method) outputting the number of unique values in a list (line/row) like the UNIX command 'uniq'?
Lars

[MrProgrammer]

Data Pilot can do that. Use the Options… button then Count. This gives you a list of the unique values and you can see how many there are.

Or, if all you need is the count and don't care what the unique values are, use array formula =SUM(1/COUNTIF(X2:X99;X2:X99)) which must be entered with ⌘⇑Enter on a Mac or Ctrl+Shift+Enter on other platforms.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Villeroy]

3rd option: Data>Filter>Standard...
column X <not empty>
[More Options]
[X] Skip duplicates

In the bottom right corner COUNT counts the numbers, COUNTA counts all non-emtpy and visible cells

[humulus]

To Villeroy,
thanks, it's probably what I need but I still have problem. The data area contains person names. I have defined a target area (one cell) named Result and I select this in the left box. In the right box I then get the address of the target area ($Tabell1.$D$13). When I don't use COUNTA I get a list of unique names starting in the Result cell. What input shall I use to get the number of unique names?
Lars

[Villeroy]

Well, OOo has a database component where this would be a query as as trivial as
SELECT COUNT(*) FROM (SELECT DISTINCT "Names" FROM "Table")

The Calc tool which comes closest to a database tool is the data pilot recommended by MrProgrammer.
It can be used to generate a table with all names, the count for each name and the total count for all names

I have defined a target area (one cell) named Result and I select this in the left box.

Select that range and name it as a database-like range (menu:Data>Define...)
Then =ROWS(MyList)-1 gives the current row count of that list without the header row.

[humulus]

Hi,
thanks. I've already tried Data pilot but there was trouble with empty cells (division by zero). I will try your method and find a solution.
Lars

[Villeroy]

The data pilot can be filtered.

[MrProgrammer]

there was trouble with empty cells (division by zero)

If some of your cells are empty — the original post didn't mention that — then you need to modify the array formula to =SUM(IF(ISBLANK(X2:X99);"";1/COUNTIF(X$2:X$99;X2:X99)))    ⌘⇑Enter on a Mac or Ctrl+Shift+Enter on other platforms.

[humulus]

Hi Mr Programmer,
your last formula works - when I avoid parenthesis and 2 question marks in one cell but that is no problem. Tanks.
Lars

[Villeroy]

Hi Mr Programmer,
your last formula works - when I avoid parenthesis and 2 question marks in one cell but that is no problem. Tanks.
Lars

Turn off Tools>Options>Calc>Regular Expressions in formulas

[Spoody Gonzalez]

Hi Mr Programmer,

Your solution works very well, thank you. I used your new method, that I wasn't aware of until now.
But I don't understand the logic of it at all.
I've read about arrays, conditional functions, etc, but you combine these in a way I don't understand. Can you explain how this working?
Thanks.

[MrProgrammer]

Suppose we have a label in X1 and the rows have in X2:X12:

   3
   1
   4
   1
   5
   9
   2
   6
   5
   3
   5

Then Array formula =SUM(1/COUNTIF(X2:X12;X2:X12)) expands to the sum of 1/COUNTIF(X2:X12;X2), 1/COUNTIF(X2:X12;X3), 1/COUNTIF(X2:X12;X4), 1/COUNTIF(X2:X12;X5), 1/COUNTIF(X2:X12;X6), …. These COUNTIFs evaluate to 1/2, 1/2, 1/1, 1/2, 1/3, 1/1, 1/1, 1/1, 1/3, 1/2, and 1/3 with a sum of 7. Numbers which occur once contribute one 1/1, numbers which occur twice contribute two 1/2's, numbers which occur thrice contribute three 1/3's, etc.

If you don't understand the array formula, don't use it. Instead put =1/COUNTIF(X$2:X$12;X2) in Y2, fill the formula down to Y12, and sum those values with =SUM(Y2:Y12). Most people find this method much easier to understand. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know if you don't understand why there are two $ characters in =1/COUNTIF(X$2:X$12;X2).

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Spoody Gonzalez]

I see. That's a nice reasoning in the formula. Thanks for the explanation!

[sunsmountain]

To add to a solved problem, a deceptively simple but effective solution is to sort the list ascending, add an extra column Y, =IF(X2=X3;0;1) , and then just SUM(Y2:Y12).