How to extract a unique distinct list from a column in LibreOffice Calc with range filter criteria

Using this method it is possible to filter a range of data with the INDIRECT function.

you will have to change the $A$2:$A$20 range two times to the wished range given by INDIRECT.

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

Let's say you have a third column, C with a header row 1 and data from row 2.
This function will work only if the data in column C is sorted.

You type your filter criteria in cell $E$1.

1. using MATCH to find the first occurrence of the filter criteria:
2. MATCH($E$1,$C$1:$C$20,0)
3. using COUNTIF to fing out how many rows have this criteria:
   COUNTIF($C$1:$C$20,$E$1)
4. the range will start in the row given by MATCH($E$1,$C$1:$C$20,0)
   and end in the row given by (MATCH($E$1,$C$1:$C$20,0)+COUNTIF($C$1:$C$20,$E$1)-1)
5. now add the letter of the data column to the range like "A"&
6. your range starting cell will be INDIRECT("A"&MATCH($E$1,$C$1:$C$20,0))
   your range ending cell will be INDIRECT("A"&(MATCH($E$1,$C$1:$C$20,0)+COUNTIF($C$1:$C$20,$E$1)-1))
7. the string you will paste in place of $A$2:$A$20 will be INDIRECT("A"&MATCH($E$1,$C$1:$C$20,0)):INDIRECT("A"&(MATCH($E$1,$C$1:$C$20,0)+COUNTIF($C$1:$C$20,$E$1)-1))

How to extract a unique distinct list from a column in LibreOffice Calc

Hi all,

to this problem: http://nabble.documentfoundation.org/Generate-Unique-List-from-Values-in-Column-td4077250.html

this is the solution in MS Excel,
http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

and to do this in LibreOffice the single difference you have to make is to not click and drag, but to ctrl+click and drag down the cell contents!

so, step by step:

you have a header: row 1.
your list starts in row 2 in column A.
your unique list will start in row 2 column B.

you type =INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) in cell B2 and hit CTRL+SHIFT+ENTER

this should result in you having one of the values of your A2:A20 list in B2.

now press CRTL and click and drag down the bottom-right corner of B2 cell.

if you drag down long enough, #N/A cell contents should show up on the bottom of your unique list.