1. Sort unique filtered values


=4.SORT(3.IFERROR(IFERROR(2.INDEX(UNIQUE(FILTER(Source[BATCH];(Source[DESTINY 2]="2")));MATCH(UNIQUE(FILTER(Source[BATCH];(Source[DESTINY 2]="2")));UNIQUE(FILTER(Source[BATCH];(Source[√2]="√")));0)); 1.INDEX(UNIQUE(FILTER(Source[BATCH];(Source[DESTINY 2]="2")));;)); " " );1)
  1.  If 2. returns an error it looks for filtered values from the column "BATCH" that have number "2" on column "DESTINY 2". We could use only this formula, but if we want to keep the list updated we have to use a more sophisticated and complex one;

  2.  If values from 1. have an exact match "0" with the filtered values obtained from the column "BATCH" that have the symbol "√" on column "√2", it will remove them from the list;

  3.  To have a clean result and if it returns an error it will display an "empty" value;

  4.  Sort in an ascending manner.


    5. This formula is a nested IFERROR and INDEX function that returns the value of the first cell in the column BATCH of the table SOURCE where the value in the column DESTINY 2 is equal to 2.

    If there is no such cell, it returns the value of the first cell in the column BATCH of the table SOURCE where the value in the column √2 is equal to √. If there is no such cell, it returns an empty string. The IFERROR function is used to handle errors that may occur in the INDEX function. If the INDEX function returns an error, the IFERROR function returns the value 0.

SORT

Sort - Syntax

SORT(array, [sort_index], [sort_order], [by_col])

Sorts the result in an ascending manner when you apply "1" on [sort_index].

At our table:

Destiny 2
2
6
UNIQUE

Unique - Syntax

UNIQUE (array, [by_col], [occurs_once])

The formula: "UNIQUE(FILTER(Source[BATCH]; (Source[DESTINY 2]="2")))", returns

At our table:

Destiny 2
6
2
FILTER

Filter - Syntax

FILTER(array, include, [if_empty])

The formula: "FILTER(Source[BATCH];(Source[DESTINY 2]="2"))", returns

At our table:

Destiny 2
6
2
2
INDEX

Index - Syntax

INDEX(array, row_num, [col_num], [area_num])

The formula: "INDEX(UNIQUE(FILTER(Source[BATCH]; (Source[DESTINY 2]="2")));)", excluding for now "row_num" (");)"), returns

At our table:

Destiny 2
2
6

The formula 2) : "INDEX(UNIQUE(FILTER(Source[BATCH]; (Source[DESTINY 2]="2"))); MATCH(UNIQUE(FILTER(Source[BATCH]; (Source[DESTINY 2]="2"))); UNIQUE(FILTER(Source[BATCH]; (Source[√2]="√")));0))", , returns

At our table:

Destiny 2
#CALC
#CALC
MATCH

Match - Syntax

MATCH(lookup_value, lookup_array, [match_type])

Cells of the column "Destiny 2" will become empty as soon as the cells of the column "√2" has a "√", applying the formula: IF(COUNTIFS([√2];"="&"√";[BATCH];[@BATCH])>=1;"").

At our table:

Destiny 2
#CALC
#CALC
IFERROR

Iferror - Syntax

IFERROR(value, value_if_error)

Used twice. The first it returns "empty", if error, and in the second it will calculate the formula: INDEX(UNIQUE(FILTER(Source[BATCH]; (Source[DESTINY 2]="2")));;))  if  2) returns an error.

At our table:

Destiny 2
6
2