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

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

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

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.

Fetch a value in a data set based on coordinates.

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

Create a unique distinct alphabetically sorted list extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]Extract a unique distinct list by matching items that meet a criterion

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Unique distinct values from multiple columns using array formula

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]How to create a unique distinct list where other columns meet two criteria

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]### 5 Responses to “Unique distinct list to be created from a column where an adjacent column has text cell values”

### Leave a Reply

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

<code>your formula</code>

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