## How to automatically create a unique distinct list and remove blanks

**Question:** How do I create a unique distinct list from a list containing several blanks?

**Answer:**

### Array formula in B2:

copied down as far as necessary.

### Download excel sample file for this tutorial.

unique-values-from-list-with-several-blanks.xls

(Excel 97-2003 Workbook *.xls)

### Functions used in this article:

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**ISBLANK(**value**)
**Checks whether a reference is to an empty cell and returns TRUE or FALSE

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

### 23 Responses to “How to automatically create a unique distinct list and remove blanks”

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

i cannot use an array formula for certain reasons. is there any other way?

Can you use a vba macro? Maybe someone at http://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?

http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

http://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.

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.

http://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.