Hello @adrianbucks, you could use a Filter to list, or copy, all the distinct rows from a range in Calc.
Manually:
1. Select the entire Range to find distinct rows in ( e.g. "A1:B99" ),
2. Choose the menu "Data : More Filters : Standard Filter...",
3. In the dialog box that appears, in the first row of the Filter Criteria, set the Field Name to "- none -",
4. Expand the Options by clicking on the small triangle,
5. Check the checkbox "No duplications",
6. Check the checkbox "Copy results to:" and enter a full Target CellAddress into the textbox ( e.g. "Sheet1.D1" ),
7. Uncheck the checkbox "Keep filter criteria",
8. If your source range does not contain a header, uncheck the checkbox "Range contains column labels",
9. If case matters while searching for distinct rows, check the checkbox "Case sensitive",
10. Click OK.
By Macro:
Sub filterDistinct( strSourceRange As String, strTargetCell As String, Optional bContainsHeader As Boolean, Optional bCaseSensitive As Boolean )
REM Uses a Filter to copy distinct rows from the specified Source Range into a new Range that starts from the specified Target Cell.
REM <strSourceRange> : specifies the Range to find distinct rows in, e.g. "A1:B99".
REM <strTargetCell> : specifies the Cell to put the first found distinct row in, e.g. "D1".
REM <bContainsHeader> : OPTIONAL - pass TRUE if the Source Range contains a Header.
REM <bCaseSensitive> : OPTIONAL - pass TRUE if case matters while searching for distinct rows.
Dim oSheet As Object, oSourceRange As Object, oFilter As Object
oSheet = ThisComponent.CurrentController.ActiveSheet
oSourceRange = oSheet.getCellRangebyName( strSourceRange )
oFilter = oSourceRange.createFilterDescriptor( True )
oFilter.SkipDuplicates = True
oFilter.CopyOutputData = True
oFilter.OutputPosition = oSheet.getCellRangebyName( strTargetCell ).CellAddress
If Not IsMissing( bContainsHeader ) Then oFilter.ContainsHeader = bContainsHeader
If Not IsMissing( bCaseSensitive ) Then oFilter.IsCaseSensitive = bCaseSensitive
oSourceRange.filter( oFilter )
End Sub
Example call : filterDistinct( "A2:A10", "B2" )
And the "something for Excel" is ...?
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$10) + (COUNTIF($A$2:$A$10, $A$2:$A$10)<>1), 0)), "")
Where $A$2:$A$10 is the source list and $B$1:B1 is the column header where the formula is inserted starting from B2
Would you mind to explain what column is the one from which you want to get the distinct values, and what contents are expected to reside in the other column of the two occurring in the formula?The above comment crossed mine in time. I cannot get a reasonable meaning, however.
An explanation of the structure of this Excel formula can be found here.
Finally some explanation, thanks.
Fwiw, all the formula expressions given there work for me with the example of "Ronnie";"David";... if it doesn't work in your case you're likely doing something different (or you're using an older version in which it doesn't work, I tried 5.2 and 5.3)
@erAck: Suppose the formula was not entered for array-evaluation. As a one -cell-array-formula correctly filled down as far as needed it works for me.