Extract a unique distinct list and ignore blanks
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells.
Table of contents
1. Extract a unique distinct list and ignore blanks
Question: How do I extract a unique distinct list from a column containing blanks?
Answer:Â Cell range B3:B12 contains several blank cells. The following formula in cell D3 extracts unique distinct values from cell range B3:B12. Unique distinct values are all values except duplicates are merged into one distinct value.
Formula in D3:
Copy cell B2 and paste to cells below.
1.1 Explaining the LOOKUP formula in cell D3
Step 1 - Check cell range B3:B12 for non-empty cells
If a cell contains a value TRUE is returned. The following line is a logical expression, cells not equal to nothing return TRUE. The less and larger than characters are logical operators that evaluates to boolean values, True or False.
$B$3:$B$12<>""
returns {TRUE;TRUE; ... ;TRUE}
Step 2 - Ignore duplicate cells
The COUNTIF function counts cells that equal a condition or any of the supplied criteria. The first argument has both an absolute and relative cell reference. This allows the cell range to grow when cell B3 is copied to cells below as far as needed.
COUNTIF($D$2:D2, $B$3:$B$12)=0
The equal sign is also a logical operator like the less and greater signs, it evaluates tor True or False.
{0;0;0;0;0;0;0;0;0;0}=0
returns {TRUE; TRUE; ...; TRUE}
Recommended articles
Counts the number of cells that meet a specific condition.
Step 3 - Multiply arrays
Multiplying boolean values is the same as applying OR logic to each value based on their position.
The first value in the first array is True and in the second array is also True. True * True equals 1.
The other possibilties are:
- True * False = 0 (zero)
- False * True = 0 (zero)
- False * False = 0 (zero)
The boolean equivalent to True is 1 and False is 0 (zero).
(COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")
returns {1; 1; 0; 1; 1; 1; 0; 1; 1; 1}
Step 4 - Divide 1 by the array
The reason I am dividing 1 with the array is to replace 0 (zero) with the #DIV/0. The LOOKUP function will ignore the #DIV/0 errors, shown in the next step.
1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>""))
returns {1; 1; #DIV/0!;... ; 1}
Step 5 - Find last match in array and return corresponding value
LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")), $B$3:$B$12)
returns "EE" in cell D3.
Recommended articles
Extract a unique distinct list from two columns
Vlookup – Return multiple unique distinct values
Extract a unique distinct list sorted from A to Z
Extract a unique distinct list from three columns
Extract unique distinct values from a multi-column cell range
Extract unique distinct values A to Z from a range and ignore blanks
Category 'Unique distinct values'
2. Extract a unique distinct list and ignore blanks - Excel 365
Update 10th December 2020: Excel 365 subscribers can now use this regular formula in cell D3.
There is no need to use absolute cell references with formulas that return a dynamic array, however, it is crucial that you use them with the first formula above, as shown.
Note that the formula above deploys an array of values to the appropriate cell range. If any of the cells below are populated cell D3 returns a #SPILL! error.
Check out how the Excel 365 formula works here:
Extract unique distinct values ignoring blanks
Recommended articles
FILTER function | UNIQUE function | LET function | XMATCH function | XLOOKUP function | Excel Function Library
3. Extract a unique distinct list and ignore blanks (UDF)
The image above shows a User Defined Function that extracts unique distinct values from a specific cell range.
What is a unique distinct value?
Unique distinct values are all values, however, duplicate values are merged into one distinct value. In other words, there are no duplicate values in the extracted list.
What is a User Defined Function (UDF)?
A UDF is a custom function that you can create yourself using Visual Basic for Applications (VBA) code. The code must be inserted into a code module in your workbook before you can use the custom function.
Formula in cell D3:
3.1 User Defined Function Syntax
FilterUniqueSort(rng)
3.2 User Defined Function arguments
rng -Â A reference to a cell range you want to extract values from. The example above uses cell reference B3:B12.
3.3 VBA code
'Name User Defined Function and define paremeter Function FilterUniqueSort(rng As Range) 'Dimension variables and declare data types Dim ucoll As New Collection, Value As Variant, temp() As Variant Dim iRows As Single, i As Single 'Redimension array variable ReDim temp(0) 'Enable error handling On Error Resume Next 'Iterate through each value in range For Each Value In rng 'Check if number of characters in value is greater than 0 (zero), if true add value to collection ucoll If Len(Value) > 0 Then ucoll.Add Value, CStr(Value) 'Continue with next value Next Value 'Disable error handling On Error GoTo 0 'Iterate through each value in collection ucoll For Each Value In ucoll 'Save value to last container in array variable temp temp(UBound(temp)) = Value 'Add new container to array variable temp ReDim Preserve temp(UBound(temp) + 1) 'Next value Next Value 'Remove last container in array variable temp ReDim Preserve temp(UBound(temp) - 1) 'Transpose values in array variable temp and return those values to worksheet FilterUniqueSort = Application.Transpose(temp) End Function
3.4 Where to put the code?
- Press shortcut keys Alt + F11 to open the Visual Basic Editor (VBE).
- Press the left mouse button on "Insert" on the menu, see the image above. A pop-up menu appears.
- Press the left mouse button on "Module" to create a module to your workbook.
- Copy (Ctrl + c) above VBA code
- Paste (Ctrl +v) Â to the code module, see the image above.
- Return to Excel.
Recommended reading
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Substitute multiple text strings [UDF]
SUMIF across multiple sheets [UDF]
List files in a folder and subfolders [UDF]
Category 'User Defined Functions'
4. Excel file
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Excel categories
33 Responses to “Extract a unique distinct list and ignore blanks”
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.
i cannot use an array formula for certain reasons. is there any other way?
Can you use a vba macro? Maybe someone at https://www.excelforum.com/ can help you.
Thanks sooooo much man! U Have saved my life!!!!
great, but keep getting a #NUM! error after cell b2
any reaason this would calculate REALLY slowly?
brendan,
Array formulas working with large data sets can be slow. It also depends on your computer hardware.
Hi
I'm getting a problem when i using your formula in two different sheet. Although its working in the single sheet.
On same sheet reference.
+ CTRL + SHIFT + ENTER
I'm using this on different sheet reference.
+ CTRL + SHIFT + ENTER
Plz correct my formula.
Abhinav,
Try this formula on the other sheet:
That worked like a charm... :)
Thanks so much Oscar...!!!
Hi Oscar, have been reading your blog over the last few weeks and have learnt a bunch of new techniques as a result.
I have been working with one of the unique list array formulas and it works a treat. I was wondering however if it is possible to add a criteria outside the array to effectively filter the list by a further level? The formula I have is
=IFERROR(INDEX(STATSUBCAT, MATCH(MIN(IF(ISBLANK(STATSUBCAT)+COUNTIF(D7:$D$7, STATSUBCAT), "", IF(ISNUMBER(STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT)+SUM(IF(ISNUMBER(STATSUBCAT), 1, 0))+1))), IF(ISBLANK(STATSUBCAT)+COUNTIF(D7:$D$7, STATSUBCAT), "", IF(ISNUMBER(STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT), COUNTIF(STATSUBCAT, "<"&STATSUBCAT)+SUM(IF(ISNUMBER(STATSUBCAT), 1, 0))+1)), 0)),"")
I'm working (as a learning process!) on a personal budget spreadsheet. Using bank statements and the adding a column to categorize the transactions then SUMIF in the adjacent column I have a helpful look into my total catergory spending.
At the moment I am getting what the array formula is supposed to do; provide a list of all unique category types. But say I wanted to see what categories appeared between 2 dates? I tried adding a simple IF statement into the array formula just to select one date to see if I could get any matches to return. It did not so I guess I'm inserting the statement at the wrong position assuming this is possible at all?
Thanks again for a taking the time to put together such an instructive site.
To save anyone looking into this I should say I had my problem on its head. I had all I needed all along and simply needed to add a SUMIFS formula to filter the original data.
Just confirms stepping away from a problem for an hour or so is often the best thing you can do.
Duncan,
Yes, you can use sumifs. Have you tried a pivot tables?
https://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
https://www.contextures.com/CreatePivotTable.html
Hi Oscar,
I'm thinking pivot tables will have to be on my to learn list but for now it's info overload and I'm still getting to grips with some of the other stuff first. Also I have an awful lot of catergories in my Unique list that certainly benefit in terms of screen real estate from just displaying the ones needed to display the results.
To which end can could you help me abbreviate the formula so that it is only concerned with text entries and blanks (I'm only using the formula to create the unique category and subcategory expense description). I've noticed as this workbook is developing the CPU usage is starting to get out of hand on this moderate i3 3.2ghz machine. I'd rather not have to resort to manual calculation if I can help it.
I've stripped all the volatile date filtering out, TODAY() etc and that doesn't appear to be the source of the high CPU call?
I keep breaking the formula when I have tried to strip out the number functions, I hopefully as I get more familiar with the syntax I'll be able to say I have a better degree of understanding of how all the parts sit together.
Best,
Doh! Sorry Oscar were you suggesting I keep the Array for the Unique value then use those as row labels for the different column headers?
The jpg attached is an early draft and so far I've only SUMIFS the date ranges for the main expense catergory, the next block is the subcatergory to the right will hopefully receive the same treatment CPU overhead allowing.
Can I employ a Pivot table when the Catergory descriptions may change. I suppose in time all catergories will appear but that said I am really liking the idea of this unique array deal.
If you think its possible I'll be happy to go off and do the proper researh
Thanks again
Duncan,
Pivot tables lets you create unique values and sum corresponding values. It is really easy.
You can also filter the data using dates.
Oscar,
How right you were. What a revelation!
Took me a while to spot the Group.. function to assist in gathering my Date column headers into months but once I did I now have everything I need and at hardly any CPU cost.
I am now looking at the multiple consolidation ranges to see if this will allow me to get the necessary summary overview of all account pivot tables.
Thanks again..
wow man, u blew me away.. i've been looking for this for a long time. ur algorithm is bright. Ive tried PIVOT TABLE but it wont update if u copy it, ADVANCED FILTER but u have to do it over and over. im using a multiple table report and this what im trying to create but no luck. u did great bro.. ive search about circular references coz i know that would do it but its too complex for my level.. thanks bro. ur the man!!!
Whenever I use this I keep getting a blank in the second row for the created list.
Other than that it is working fine. I just don't understand what would cause that.
Thanks! It worked for me. I used this to remove blank spaces from a total of 2520 entries.
If it is an option, I have gotten in the habit of using this formula:
=IFERROR(INDEX(A:A,MATCH(0,INDEX(COUNTIF($B$1:B3,A:A),0,0),0)),)
And simply starting in B4 with B1=BLANK B2=0 and B3="Header"
That way it reads blanks and 0s as non-unique values in the list, and they are ignored. I will then hide rows 1:2.
The formula looks for values in the range (A:A in the example) not previously occurring in the list above the current cell ($B1:$B3 in the example). B4 examines $B$1:$B3, B5 examines $B$1:$B4 and so on.
Perhaps not the most elegant way, but it gets the job done and does not require arrays or more complex formulae.
Nice Formula/Solution!
Is there a way to apply this to a filtered range and have it ignore hidden values? I'm using this formula and it works really well, but it shows every unique value in my range (R12:R1200) including the hidden ones. Any ideas?
=INDEX($R$12:$R$1200, MATCH(0, IF(ISBLANK($R$12:$R$1200), 1, COUNTIF(Z2:$Z$2, $R$12:$R$1200)), 0))
Hi Rod,
Yes, there is.
https://www.get-digital-help.com/2011/11/02/extract-unique-distinct-values-from-a-filtered-table-udf-and-array-formula/
Thank you, Oscar! That worked! I'll rely to that post with a related question.
This formula doesn't work for at all - I either get an entire list of n/a, or a value that doesn't even exist on my list!
I have a column of dates: I just want another column to show me a list of all unique dates in the first column.
Joy
You need to adjust the cell references in the formula so they point to your data:
=LOOKUP(2, 1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")), $B$3:$B$12)
Cell reference $D$2:D2 is different, if you enter the formula in cell G5 the cell reference becomes $G$4:G4. meaning it should always be a cell reference to the adjacent cell right above.
You then copy the cell and paste it to cells below as far as needed, this will automatically change the relative cell references in the formula.
Hey! So Excel has a "UNIQUE" function now. Do you please have any ideas on how to extract them from that function?
Andrew,
yes, use the FILTER function to exclude blank cells.
=UNIQUE(FILTER(B3:B12,B3:B12<>""))
Hello Oscar,
Great and very helpful website!
I am looking to return a unique list across multiple columns and rows. I had assumed I could just expand
$B$3:$B$12to my criteria$A$5:$G$30but this just seems to return #N/AIs there a way of obtaining a unique list based from multiple columns and rows?
What I am ultimately looking to do is list them in the order of most frequent result at the top
Ollie,
Is there a way of obtaining a unique list based from multiple columns and rows with most frequent result at the top?
Yes, there is. Read this:
Extract a unique distinct list across multiple columns and rows sorted based on frequency
Have been using the UDF to great effect but I have been asked if there is a way to add a list of other items to exclude along with the blanks
I know I can modify the line -
If Len(Value) > 0 Then ucoll.Add Value, CStr(Value) to include
If Len(Value) > 0 And Value "Cancelled" And Value "N/A" Then ucoll.Add Value, CStr(Value)
but this is rather inelegant there must be a way to add an array or list to the top of the UDF which would be easier to manage but my VBA is a little to rusty to figure it out
Hi Oscar,
This doesn't work for a range across multiple columns. Do you know if it's a small adjustment to the formula to allow this or will it only work for a single column?
Kristo,
those formulas above work only for a single column, the following Excel 365 formula works with a multi-column cell range:
This formula works with many multicolumn cell ranges