Question: I want a unique list to be created from a column where an adjacent column has text cell value?

unique-list-where-an-adjacent-column-has-text-cell-values6

Answer: 

unique-list-where-an-adjacent-column-has-text-cell-values44

Array formula in D3:

=INDEX($A$2:$A$17, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE), 0))

Recommended articles

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Unique distinct list sorted alphabetically and based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Create unique distinct list from column where an adjacent column meets criteria

Question: I want a unique list to be created from a column where an adjacent column has cell values between […]

How to create an array formula

  1. Copy array formula (Ctrl + c)
  2. Double click cell D2
  3. Paste array formula (Ctrl + v)
  4. Press and hold Ctrl + Shift
  5. Press Enter

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Explaining formula in cell D3

Step 1 - Check if adjacent values are not text values

ISTEXT($B$2:$B$17)=FALSE

becomes

ISTEXT({"DD";"VV";0;"EE"; "CC";"DD";"VV";0;"EE";"CC";0; "DD";"VV";0;"EE";"CC"})=FALSE

becomes

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

and returns

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

Step 2 - Check if value is unique

COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE)

becomes

COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE)

becomes

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}+(ISTEXT($B$2:$B$17)=FALSE)

becomes

{0;0;0;0;0;0;0;0;0; 0;0;0;0;0;0;0}+{FALSE;FALSE;TRUE; FALSE;FALSE;FALSE;FALSE;TRUE;FALSE; FALSE;TRUE;FALSE;FALSE; TRUE;FALSE;FALSE}

and returns

{0;0;1;0;0;0;0;1;0;0;1;0;0;1;0;0}

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

Step 3 - Find relative position

MATCH(0, COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE), 0)

becomes

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

and returns 1.

How to use Excel’s MATCH function

Identify the position of a value in an array.

Step 4 - Return value

INDEX($A$2:$A$17, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE), 0))

becomes

INDEX($A$2:$A$17, 1)

and returns 12 in cell D3.

INDEX function explained

Fetch a value in a data set based on coordinates.

Download excel example file
unique-list-to-be-created-from-a-column-where-an-adjacent-column-has-text-cell-values2.xls
(Excel 97-2003 Workbook *.xls)

 

This blog article is one out of thirteen articles on the same subject "unique".