Author: Oscar Cronquist Article last updated on December 04, 2018

Question: How do I create a unique distinct list from a column sorted by occurrence?

Array formula in D3:

=INDEX($B$3:$B$15, MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*COUNTIF(D$2:$D2,$B$3:$B$15)<>1)=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*IF(COUNTIF(D$2:$D2, $B$3:$B$15)=1,0,1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1),0))

copied down as far as needed.

How to create an array formula

  1. Copy above array formula
  2. Select cell D3
  3. Click in formula bar
  4. Paste array formula in formula bar
  5. Press and hold Ctrl + Shift
  6. Press Enter

Formula in E3:

=COUNTIF($B$3:$B$15, D3)

copied down as far as needed.

Explaining formula in cell D3

Step 1 - Calculate frequency of each value

The COUNTIF function counts values based on a condition, in this case, multiple conditions.

COUNTIF($B$3:$B$15, $B$3:$B$15)

becomes

COUNTIF({"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"}, {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})

and returns

{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}.

Step 2 - Prevent duplicate values

The first argument in the COUNTIF function contains an expanding cell reference. It makes sure that prior values are not taken into account again.

COUNTIF(D$2:$D2,$B$3:$B$15)<>1

becomes

COUNTIF("Unique distinct
list based on occurances", {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})<>1

becomes

{0;0;0;0;0;0;0;0;0;0;0;0;0}<>1

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 3 - Multiply arrays

COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2,$B$3:$B$15)<>1)

becomes

{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}* {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{1;2;3;2;2;1;1;1;3;1;1;2;3}.

Step 4 - Check if largest value is equal to 0 (zero)

The IF function returns 1 if number is not equal to 0 (zero) and the largest value in array if equal to zero using the MAX function.

IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(MAX({1;2;3;2;2;1;1;1;3;1;1;2;3})=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(3=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(FALSE, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(FALSE, 1, 3)

and returns 3.

Step 5 - Find position in array

The MATCH function returns the position of a value in a cell range or array.

MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))

becomes

MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)

becomes

MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)

becomes

MATCH(3, {1;2;3;2;2;1;1;1;3;1;1;2;3}, 0)

and returns 3.

Step 6 - Return value

The INDEX function returns a value based on a row number (and column number if needed).

INDEX($B$3:$B$15, MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*COUNTIF(D$2:$D2,$B$3:$B$15)<>1)=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*IF(COUNTIF(D$2:$D2, $B$3:$B$15)=1,0,1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))

becomes

INDEX($B$3:$B$15, 3)

and returns "GG" in cell D3.

Download Excel *.xlsx file

Unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel.xlsx

Related articles