## Extract a unique distinct list and remove blanks

**Question:** How do I create a unique distinct list from a list containing several 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.

**Formula in 32:**

Copy cell B2 and paste to cells below.

Recommended article

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

### Explaining 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, if cells is not equal to nothing return TRUE.

$B$3:$B$12<>""

becomes

{"AA";"AA";0;"BB";"CC";"DD";0;"BB";"EE";"EE"}<>""

and returns

{TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE}

#### Step 2 - Ignore duplicate cells

COUNTIF($D$2:D2, $B$3:$B$12)=0

becomes

COUNTIF("Unique distinct list",{"AA";"AA";0;"BB";"CC";"DD";0;"BB";"EE";"EE"})=0

becomes

{0;0;0;0;0;0;0;0;0;0}=0

and returnsÂ {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

#### Step 3 - Multiply arrays

(COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>"")

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*Â {TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE}

and returnsÂ {1;1;0;1;1;1;0;1;1;1}

#### Step 4 - Divide 1 with array

1/((COUNTIF($D$2:D2, $B$3:$B$12)=0)*($B$3:$B$12<>""))

becomes

1/({1;1;0;1;1;1;0;1;1;1})

and returnsÂ {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;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)

becomes

LOOKUP(2, {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;1}, $B$3:$B$12)

becomes

LOOKUP(2, {1;1;#DIV/0!;1;1;1;#DIV/0!;1;1;1}, {"AA";"AA"; 0;"BB";"CC"; "DD";0;"BB"; "EE";"EE"})

and returns "EE" in cell D3.

How to use the LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

### Download excel sample file for this tutorial.

unique distinct values from list with several blanks.xlsx

(Excel 2007 and later versions, workbook *.xlsx)

Recommended article

Create a unique distinct sorted list containing both numbers text removing blanks

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

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

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

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Extract a unique distinct list sorted alphabetically and ignore blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

How to create a unique distinct list based on two conditions

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

### 24 Responses to “Extract a unique distinct list and remove 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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 downloaded 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.