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

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

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}

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

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), […]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 add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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