## Create a unique distinct list where a corresponding column has text cell values

*Article last updated on February 06, 2018*

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

**Answer: **

**Array formula in D3:**

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 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 a unique distinct list based on 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**

- Copy array formula (Ctrl + c)
- Double click cell D2
- Paste array formula (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter

Recommended article

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}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

**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.

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.

Gets a value in a specific cell range based on a row and column number.

**Download excel example fil****e**

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".*

**How to extract a unique distinct list from a column in excel**

**Extract a unique distinct list from two columns using excel 2007 array formula**

**Extract a unique distinct list from three columns in excel****Extract distinct unique sorted year and month list from a date series in excel**

**Create a unique distinct list from a date range in excel**

**Unique values from multiple columns using array formulas**

**Extract a unique distinct list sorted from A-Z from range in excel**

**Sort a range by occurence using array formula in excel**

**Filter unique distinct values from two ranges combined in excel 2007****Create a unique list and sort by occurrances from large to small**

**Unique list to be created from a column where an adjacent column has text cell values**

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

**How to create a unique distinct list where other columns meet two criteria**

Filter unique distinct values, sorted and blanks removed

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]

Extract and sort text cells from a range containing both numerical and text values

Array formula in B16: =INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), […]

Return value if it exists more then n times

Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

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. […]

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 […]

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

### 5 Responses to “Create a unique distinct list where a corresponding column has text cell values”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

When I try to put the distinct list on another sheet and copy down, it will not work. In downloaded sample there are {} that are at the beginning and the end, but when I copy and past and enter the sheet reference, those brackets go away, and the wheels fall off the bus. Can't figure out how to make it work.

Kevin,

thanks for bringing this post to my attention!

I have simplified the array formula and you will also find instructions on how to create an array formula.

Sir

Im having two excel files.In first file there are ID(column1),data1(column2),data2(column3),..data12(column12)...In second file Im having only ID(column1)(these ID will be present in first file).Now i want to extract the datas for these ID's from the first file.Please help me to get through this problem....because the file size also bigger it is tough to use ctrl+F...

harinishree,

Use the array formula provided here:

https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple

[...] then copy it across and down. This solution was taken from here and slightly altered to suit...... Unique list to be created from a column where an adjacent column has text cell values | Get Digital ... I hope that helps. Good luck. [...]