Filter unique values from a cell range
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog post using an array formula.
If you are looking for filtering unique distinct values sorted from A to Z, see this blog post: Extract a unique distinct list sorted from A-Z from range in excel
Array formula in cell B8:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Explaining formula in cell B8
Step 1 - Identify unique values
The COUNTIF function counts values based on a condition or criteria, if number is 1 then value must be unique.
COUNTIF($B$2:$E$5, $B$2:$E$5)=1
becomes
COUNTIF({"Banana", "Orange", "Pineapple", "Lemon";"Grapefruit", "Watermelon", "Apple", "Orange";"Pear", "Blueberry", "Grapefruit", "Blackberry";"Pineapple", "Blueberry", "Cranberry", "Banana"}, {"Banana", "Orange", "Pineapple", "Lemon";"Grapefruit", "Watermelon", "Apple", "Orange";"Pear", "Blueberry", "Grapefruit", "Blackberry";"Pineapple", "Blueberry", "Cranberry", "Banana"})=1
becomes
{2,2,2,1;2,1,1,2;1,2,2,1;2,2,1,2}=1
and returns
{FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE}.
Step 2 - Keep track of previous values
The first argument in the COUNTIF function contains an expanding cell reference, it grows when the cell is copied to cells below. This makes the formula aware of values displayed in cells above.
COUNTIF(B7:$B$7, $B$2:$E$5)
becomes
COUNTIF("Unique values", {"Banana", "Orange", "Pineapple", "Lemon";"Grapefruit", "Watermelon", "Apple", "Orange";"Pear", "Blueberry", "Grapefruit", "Blackberry";"Pineapple", "Blueberry", "Cranberry", "Banana"})
and returns
{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}.
Step 3 - Add arrays
First we add the arrays and then we check if a number is equal to 1. We then know that the value has not been shown and that it must be unique.
(COUNTIF($B$2:$E$5, $B$2:$E$5)=1)+COUNTIF(B7:$B$7, $B$2:$E$5))=1
becomes
({FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE}+{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0})=1
becomes
{0,0,0,1;0,1,1,0;1,0,0,1;0,0,1,0}=1
and returns
{FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE}.
Step 4 - Replace TRUE with unique number
The IF function returns unique number if boolean value is TRUE. FALSE returns "" (nothing).
IF(((COUNTIF($B$2:$E$5, $B$2:$E$5)=1)+COUNTIF(B7:$B$7, $B$2:$E$5))=1, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")
becomes
IF({FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE}, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")
This part of the formula: (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1 creates a unique value for each cell in cell range. This makes it easier to extract the correct value in a later step.
IF({FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE}, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, "")
becomes
IF({FALSE, FALSE, FALSE, TRUE;FALSE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, TRUE;FALSE, FALSE, TRUE, FALSE}, {2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, "")
and returns
{"","","",2.16666666666667;"",3.25,3.2,"";4.33333333333333,"","",4.16666666666667;"","",5.2,""}
Step 5 - Find smallest value in array
The MIN function returns the smallest number in array ignoring blanks and text values.
MIN(IF(((COUNTIF($B$2:$E$5, $B$2:$E$5)=1)+COUNTIF(B7:$B$7, $B$2:$E$5))=1, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))
becomes
MIN({"","","",2.16666666666667;"",3.25,3.2,"";4.33333333333333,"","",4.16666666666667;"","",5.2,""})
and returns 2.16666666666667.
Step 4 - Find corresponding value
IF(MIN(IF(((COUNTIF($B$2:$E$5, $B$2:$E$5)=1)+COUNTIF(B7:$B$7, $B$2:$E$5))=1, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, $B$2:$E$5, "")
becomes
IF(2.16666666666667=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, $B$2:$E$5, "")
becomes
IF(2.16666666666667={2.33333333333333, 2.25, 2.2, 2.16666666666667;3.33333333333333, 3.25, 3.2, 3.16666666666667;4.33333333333333, 4.25, 4.2, 4.16666666666667;5.33333333333333, 5.25, 5.2, 5.16666666666667}, $B$2:$E$5, "")
becomes
IF({FALSE, FALSE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, $B$2:$E$5, "")
and returns
{"","","","Lemon";"","","","";"","","","";"","","",""}.
Step 5 - Concatenate strings in array
The TEXTJOIN function returns values concatenated ignoring blanks in array.
TEXTJOIN("", TRUE, IF(MIN(IF(((COUNTIF($B$2:$E$5, $B$2:$E$5)=1)+COUNTIF(B7:$B$7, $B$2:$E$5))=1, (ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, ""))=(ROW($B$2:$E$5)+(1/(COLUMN($B$2:$E$5)+1)))*1, $B$2:$E$5, ""))
becomes
TEXTJOIN("", TRUE, {"","","","Lemon";"","","","";"","","","";"","","",""})
and returns "Lemon" in cell B8.
Filter unique values from a range (for older Excel versions)
Array formula in B10:
copied down as far as necessary.
Named ranges
tbl (B4:E7)
What is named ranges?
How to implement array formula to your workbook
Change the named range. If your list starts at, for example, F3. Change $B$9:B9 in the above formulas to F2:$F$2.
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Filter common values from three separate columns
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Vlookup across multiple sheets
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
Filter common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Extract unique distinct text values containing string in a range
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
Filter duplicate values from a range that begins with string
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
4 Responses to “Filter unique values from a cell range”
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.
This formula breaks if you change the size of the "tbl" range to only one or two columns of the same data in the "tbl" range.
How is this fixed so that I could use a range of any size in place of the "tbl" range?
tyler,
the formula works if you use two or more columns. I tried with two columns and it worked.
Use this formula if you are working with one column:
How to extract unique values from a column
Remember, the formulas above filter unique values. If you are looking for unique distinct values, see this post:
Extract a unique distinct list sorted from A-Z from range
I tried your formula, it came up with (") in the cell.
What could I have done wrong? Here is the formula.
=ArrayFormula(textjoin("",true,if(min(if(((countif($B$5:$EC$16,$B$5:$EC$16)=1)+countif(A36:$A$36,$B$5:$EC$16))=1,(row($B$5:$EC$16)+(1/(column($B$5:$EC$16)+1)))*1,""))=(row($B$5:$EC$16)+(1/(column($B$5:$EC$16)+1)))*1,$B$5:$EC$16,"")))
RANGE : b5:EC16
output header: A36
Regards
Sunny
Sunny Dhillon,
Try this array formula:
=TEXTJOIN("", TRUE, IF(MIN(IF(((COUNTIF($B$5:$EC$16, $B$5:$EC$16)=1)+COUNTIF(A36:$A$36, $B$5:$EC$16))=1, (ROW($B$5:$EC$16)+(1/(COLUMN($B$5:$EC$16)+1)))*1, ""))=(ROW($B$5:$EC$16)+(1/(COLUMN($B$5:$EC$16)+1)))*1, $B$5:$EC$16, ""))