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))