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

Question: How do I create a unique distinct list from a list containing several blanks?

Answer: Cell range B3:B12 contains several blank cells. The following formula in cell D3 extracts unique distinct values from cell range B3:B12. Unique distinct values are all values except duplicates.

Formula in 32:

=LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")), $B$3:$B$12)

Copy cell B2 and paste to cells below.

Recommended article

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Create a unique distinct alphabetically sorted list

Explaining formula in cell D3

Step 1 - Check cell range B3:B12 for non-empty cells

If a cell contains a value TRUE is returned. The following line is a logical expression, if cells is not equal to nothing return TRUE.

$B$3:$B$12<>""

becomes

{"AA";"AA";0;"BB";"CC";"DD";0;"BB";"EE";"EE"}<>""

and returns

{TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE}

Step 2 - Ignore duplicate cells

COUNTIF($D$2:D2, $B$3:$B$12)=0

becomes

COUNTIF("Unique distinct list",{"AA";"AA";0;"BB";"CC";"DD";0;"BB";"EE";"EE"})=0

becomes

{0;0;0;0;0;0;0;0;0;0}=0

and returns {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

Step 3 - Multiply arrays

(COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}* {TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE}

and returns {1;1;0;1;1;1;0;1;1;1}

Step 4 - Divide 1 with array

1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>""))

becomes

1/({1;1;0;1;1;1;0;1;1;1})

and returns {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;1}

Step 5 - Find last match in array and return corresponding value

LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")), $B$3:$B$12)

becomes

LOOKUP(2, {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;1}, $B$3:$B$12)

becomes

LOOKUP(2, {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;1}, {"AA";"AA"; 0;"BB";"CC"; "DD";0;"BB"; "EE";"EE"})

and returns "EE" in cell D3.

How to use the LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

How to use the LOOKUP function

Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!

Recommended article

Create a unique distinct sorted list containing both numbers text removing blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Create a unique distinct sorted list containing both numbers text removing blanks