Extract unique distinct values if adjacent cell is text
Array formula in D3:
Recommended articles
Recommended articles
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Recommended articles
This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]
Recommended articles
The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column […]
How to create an array formula
- Copy array formula (Ctrl + c)
- Double press with left mouse button on cell D2
- Paste array formula (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter
Recommended article
Recommended articles
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
The ISTEXT function returns boolean value TRUE if a cell contains a text value and FALSE if not. I am using a cell range as an argument so the function returns an array of bollean values.
They correspond to the input values based on position.
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
We need to know which values are not text values, the array is compared to boolean value FALSE. This will change TRUE to FALSE and FALSE top TRUE, the array becomes:
{FALSE;FALSE; TRUE;FALSE;FALSE; FALSE;FALSE;TRUE;FALSE;FALSE; TRUE;FALSE;FALSE; TRUE;FALSE;FALSE}
Excel handles FALSE as the same as 0 (zero) and TRUE as 1 or more.
Step 2 - Check if value is unique
The COUNTIF function counts values based on a condition or multiple conditions. It has two arguments range and criteria. The first argument is a cell reference that grows when cell is copied to cell below.
It contains a cell reference with two parts, the first one is absolute and the second is a relative cell reference. The COUNTIF function is most often used to return a single value, however, in this case, we need it to return an array of values that match the relative positions of the cell values.
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)
No values have been displayed yet so the array contains 0 (zeros) , if a value had been 1 or more it would have indicated that the value has been displayed in a cell above the current cell.
{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}
Adding the two arrays is the same as applying OR logic to the arrays meaning 0 + FALSE = 0, 0 + TRUE = 1, 1+FALSE = 1, 1+1 = 2
{0;0;1;0;0;0;0;1;0;0;1;0;0;1;0;0}
This array tells us which values that have not yet been displayed and where the adjacent value is a text value.
Recommended articles
Counts the number of cells that meet a specific condition.
Step 3 - Find relative position
The MATCH function identifies the position of the first cell value that has not yet been shown and where the adjacent value is a text value.
It matches an exact match meaning a value that is exactly equal to 0 (zer0), this is determined by the third argument.
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.
The MATCH function has identified the first value in the array as a unique distyinct value that has an adjacent cell value that contains a text value.
Recommended articles
Identify the position of a value in an array.
Step 4 - Return value
The INDEX function simply returns a value from a cell range based on a row and column coordinate. The column coordinate is not neccessary since this example has all values in a single column.
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.
Recommended articles
Gets a value in a specific cell range based on a row and column number.
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]
Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]
This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
5 Responses to “Extract unique distinct values if adjacent cell is text”
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
Paste image link to your comment.
When I try to put the distinct list on another sheet and copy down, it will not work. In the 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/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. [...]