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?
Asked
Active
Viewed 5,897 times
1 Answers
5
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.