|
LISTDISTINCTS Function
LISTDISTINCTS is an array function that returns an array of the distinct cells or row in the input data.
Options control what data will be ignored, case sensitivity, sorting the output and padding the output array.
LISTDISTINCTS Syntax
LISTDISTINCTS (theInputData, Ignore, ByRows, Case_Sense, Sort, FillType)
theInputData
theInputData can be a range or an array of constants or an expression returning an array. It identifies the data to be searched for distinct items.
Ignore (Optional)
Controls what cell values will be ignored by the distinct test.
1= Error values ignored. 2 = Blanks or Empty Cells ignored. 4 = Zero values ignored. 8 or larger nothing ignored.
Combinations of Ignore values can be made by adding the values together, although any resulting value greater than 8 means nothing will be ignored.
Default is 3 =1 & 2
ByRows (Optional)
If theInputData is a range or array with multiple columns and rows you may want either to look for distinct rows of data or to look for distinct items in all the cells.
If ByRows is specified as True (True is the default) then each row will be checked against all the other rows for uniqueness. Rows where all the columns contain items to be ignored are not treated as distinct.
If ByRows is False then each cell will be checked against all the other cells for uniqueness. Cells containing items to be ignored are not treated as distinct.
LISTDISTINCTS.SUM and LISTDISTINCT.AVG always work ByRows
The default for ByRows is TRUE.
Case_Sense (Optional)
Specifies whether the comparison will be made in a case-sensitive way (Case_Sense=TRUE) or case will be ignored (Case_Sense=FALSE). The default for Case_Sense is FALSE.
Sort (Optional)
If 0 the output list will be in the same sequence as the theInputData. If 1 the output list of distinct items/rows will be sorted ascending, or if -1 the output list of distinct items/rows will be sorted descending.
For LISTDISTINCTS.COUNT, LISTDISTINCTS.SUM and LISTDISTINCTS.AVG using Sort=2 will sort the column of counts, sums or averages ascending and Sort=-2 will sort them descending.
The default for Sort is 0 (unsorted).
FillType (Optional – default 0 fill with #N/A)
0 (Default) Fill with #N/A
1 Fill with “”
2 Fill with zeros
LISTDISTINCTS.SUM, LISTDISTINCTS.COUNT and LISTDISTINCTS.AVG are array functions that return arrays of results.
The result arrays can either be used as input to other functions or array formulas, or the functions can be entered as multi-cell array formulas so that the result arrays occupy multiple cells.
The functions follow the standard Excel rules for multi-cell array formulas:
If entered into fewer cells than the result array the excess results are not returned.
If entered into more cells than the result array the excess unused cells will be filled with whatever is specified by PadType (0=#N/A, 1=””, 2=0, default 0=#N/A)
A single-cell result will be propagated to all the excess unused cells.
A column of results will be propagated to the excess columns
A row of results will be propagated to the excess rows.
COUNTDISTINCTS and COUNTDUPES return a single number, so they do not have a PadType option.
Remarks
COUNTDISTINCTS and COUNTDUPES These functions return a single number: the total number of distinct items/rows and the total number of duplicated items/rows.
The number of duplicated items/rows is counted as the total count for each non-ignored distinct item/row -1.
They do not need to be entered as array formulas.
LISTDISTINCTS
Unless embedded in another function that will process the array:
If using ByRows the functions should be entered as a multi-cell array formula with the same number of columns as theInputData and sufficient rows for each distinct row in theInputData.
If not using ByRows the function should be entered as either a single row or single-column multi-cell array formula.
LISTDISTINCTS.COUNT, LISTDISTINCTS.SUM and LISTDISTINCTS.AVG
These functions work the same way as LISTDISTINCTS except that they produce an extra column containing the Counts, Sums and Averages respectively.
So make sure to include the extra column when you are entering them as multi-cell array formulas.
LISTDISTINCTS.SUM and LISTDISTINCTS.AVG ignore cells in the SumColumn containing True/False, numbers which are text and text.
|
