# Generating unique values in Libre Office Calc

I've got a list of entries in a range in LO Calc. The max size of the range is 1 X 9, but may include blanks and/or duplicates. How can I extract a list of unique values automatically, so that my user doesn't have to use the filter function?

• Does this q/a solve your issue? – tohuwawohu Dec 4 '14 at 18:38
• @tohuwawohu - that would be a logical approach but the question asks about a way to do it without using a filter. – fixer1234 Dec 24 '14 at 7:33

I'm not sure if your 1×9 is one row × nine columns or vice versa. I chose the latter but it should not be very difficult to transpose this array formula solution.

The array formula in C2 is,

``````=SUM(IF(ISERROR((\$A\$2:\$A\$10<>"")/COUNTIF(\$A\$2:\$A\$10; \$A\$2:\$A\$10&"")); 0; (\$A\$2:\$A\$10<>"")/COUNTIF(\$A\$2:\$A\$10; \$A\$2:\$A\$10&"")))
``````

Array formulas require Ctrl+Shift+Enter rather than simply Enter. If you plan to fill this right or down, you should adjust the absolute cell and cell range references to allow either a column or a row to 'float' in a relative manner. Once entered correctly, you can fill right or down as necessary.

EDIT¹:

OO Calc's `SUMPRODUCT` function can do this without Ctrl+Shift+Enter with this formula.

``````=SUMPRODUCT((\$A\$2:\$A\$10<>"")/COUNTIF(\$A\$2:\$A\$10&""; \$A\$2:\$A\$10&""))
``````

EDIT²:

The unique list can be gathered with a similar array formula.

The array formula in D2 is,

``````=IF(ISNA(MATCH(0; IF(\$A\$2:\$A\$10<>""; COUNTIF(D\$1:D1; \$A\$2:\$A\$10&""); 1); 0)); ""; INDEX(\$A\$2:\$A\$10; MATCH(0; IF(\$A\$2:\$A\$10<>""; COUNTIF(D\$1:D1; \$A\$2:\$A\$10&""); 1); 0)))
``````

This requires Ctrl+Shift+Enter rather than simply Enter. Once entered correctly it can be filled down to gather all unique entries.