## Create a unique distinct alphabetically sorted list, extracted from a column in excel

**Table of contents**

- Create a unique distinct alphabetically sorted list
- Create a unique distinct alphabetically sorted list with criteria

I came up with a new idea when I reread a previous article Sorting text cells using array formula in excel. Maybe it is possible to sort the new unique distinct list alphabetically.

To the right is a picture of the list I will be working with. I created named ranges, *List* (A2:A10) and a named range for the start cell of the list, *List* *_start* (A2).

Here is the formula in B2:B10. See picture below.

*Edited 2009-06-28 Here is a shorter formula:*

**Array formula in cell B2, see picture below.**

**How to create an array formula**

- Select cell B2
- Click in formula bar
- Copy and paste array formula to formula bar

- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Select cell B2
- Copy cell (not formula in formula bar) (Keyboard shortcut: Ctrl + c)
- Select cell range B3:B11
- Paste (Keyboard shortcut: Ctrl + v)

**Remove errors (Excel 2007), array formula:**

and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z.

How to customize formula to your workbook

Change named ranges in formula.

### Download excel sample file for this tutorial

unique-list-sorted-alphabetically.xls

(Excel 97-2003 Workbook *.xls)

### Create a unique distinct alphabetically sorted list with criteria

**Array formula in cell D2:**

**Array formula in cell E2:**

### Download excel *.xlsx file

Create a unique distinct alphabetically sorted list with criteria.xlsx

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

**SMALL(**array,k**)**

returns the k-th smallest number in this data set.

**ROW(**reference**)**

returns the rownumber of a reference

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

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

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

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

**COUNT(**value1, [value2]**)
**Counts the number of cells in a range that contain numbers

Great formula. With your formulas, I always modify the formula to deal with blank spaces. My data usually contains blank rows. =INDEX(List,MATCH(MIN(IF(ISBLANK(List),"",IF(COUNTIF($B$1:B1,List)=0,1,MAX((COUNTIF(List,"<"&List)+1)*2))*(COUNTIF(List,"<"&List)+1))),IF(ISBLANK(List),"",COUNTIF(List,"<"&List)+1),0). At the isblank place, you can also add other conditions like a unique list where the letter for example begins with z.

To do a descending list, change "".

This does work with numbers. Is there a way to get this to work with numbers and text?

I meant to say it does not work with numbers.

I have tried to make this work with numbers, by using an if condition to coerce a range into text -(if(isnumber(list),text(list,"0"... but I found out that countif cannot coerce a range.

Sean,

See this post: http://www.get-digital-help.com/2009/06/06/sorting-numbers-and-text-cells-also-removing-blanks-using-array-formula-in-excel/

Oscar, Thanks. That is a great formula. Is there a way to make it so it can create a unique distinct list?

See this post: http://www.get-digital-help.com/2010/07/11/create-a-unique-distinct-sorted-list-containing-both-numbers-text-removing-blanks-in-excel/

[...] http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from... [...]

Thanks for the formula. How do I extend it beyond 10 lines?

If I copy the formula from B2 and Ctrl+Shift+Enter it into B11, B12 etc. I just see "BB" repeated.

--Thanks.

Jay,

Copy the cell, not the formula.

The formula contains relative cell references that change when you copy the cell.

Oscar.

I am using the formula for creating the list with criteria and I am running into a problem. My data is on a different sheet to where I am using the formula and I have referenced the cell 'name' and 'task'. I want to create a list of names based on the task they are doing. I have copied the formula and have entered it as an array and it has the { } brackets around it. All I am getting is one name repeated down the list. I'm not sure where I am going wrong.

Thanks, Ollie.

Ollie Wood,

I think you found the answer above. Copy the cell, not the formula.

Here it is again in greater detail:

1. Select the cell

2. Copy cell (Ctrl + c)

3. Select the cell range below.

4. Paste (Ctrl + v)

The formula contains relative cell references that change when you copy the cell.

Oscar

Thanks for the quick reply. I had read this and this is how I am coping it, using ctr c and v. It's still not right though. Can you offer any other pointers where I may be going wrong?

Thanks, Ollie.

Ollie Wood,

Send me your workbook (without sensitive data):

Contact form

Hi Ollie, I had the same problem, you have to remove any blank cells from the list, or use the modified version of the formula that deals with blanks.

Oscar is it possible to add something to the sample sheet so the formula ignores blank cells? I've tried adapting it myself but with no luck.

Rob,

Read this post:

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

Hello, Oscar!

It is exciting material to me, I thought of something like that before but was not able to invent it.

But I cannot understand it. :(

For example - why are we making Max of CountIf result? As CountIf result should be a single value, than Max of it is going to be the same value isn't it?

Alexander,

why are we making Max of CountIf result? As CountIf result should be a single value, than Max of it is going to be the same value isn't it?No, The countif function returns an array of values.

Download the attached file and "Evaluate" the formula.

Wow interesting tool, thanks you! You opened me eyes!

The only issue "Evaluate" have is its unusable window - it doesn't format formula in any way, it dosn't allow copy, it doesn't allow resize.

In general it is just magic, thanks.

If you have time and will to answer -

Why

ROW(List)

works differently when I bring it out of the whole formula?

ROW(List) always return me row of the list start not the corresponding item's row.

Alexander

List is named range, example A1:A10.

Example 1,

Array formula: =ROW(List)

returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} but only 1 is visible in the cell.

Example 2,

Array formula: =IF(ROW(List)=1,1,0)

becomes

=IF({1; 2; 3; 4; 5; 6; 7; 8; 9; 10}=1,1,0)

becomes

=IF({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE},1,0)

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

but only the first value in the array is visible. A single cell can´t display all values in an array.

Thank you, very intresting!

Hope I don't mess here too much.

Wanted to ask - where did you learn excel that good from? May you suggest some good book or web resource.

As for me - I like Excel, use it where possible, but I'm not even close.

Hi Oscar,

Thank you so much for your very helpful posts - I look forward to receiving each one and find them very educational and useful.

I have a question about hyperlinks. I have a main table (which holds all my income and spend information) and I scan every receipt and put a hyperlink on the supplier of items I purchase which enables me to call up the receipt (which I save in pdf format).

I have built sheets using array formulas that query the main table based on dropdown boxes to identify record numbers meeting whatever criterion I want, and then display details of the records (using a simple offset function against the record number). However, to see any receipts, I have to go back to the main table and find the original entry to access the hyperlink.

Is there any way to display the hyperlink along with the other information I pull from the master table, so that I can access the receipts from the query sheet?

Thanks so much for your help.

Regards

Geoff Robb,

Save your receipt´s filepaths and file names in a column on your main table. I believe you can query and insert that data in your hyperlink formula on your query sheet.

Oscar,

Great work on the excellent posts. They are very helpful.

I am trying to use an adaptation of this idea, but am not able to get the formula working. In the first column I have a list that should be unique but may be unsorted. In the second column I have a Yes/No value to choose whether or not to include that row in the final result list. The final result list should be sorted. Is this possible?

For example:

List Include

AA Yes

CC Yes

BB No

DD Yes

EE No

Final result list:

AA

CC

DD

Thanks for your help!

Regards

Glen,

Array formula in cell G2:

=INDEX($D$2:$D$7, MATCH(MIN(IF((COUNTIF($G$1:G1, $D$2:$D$7)=0)*

($E$2:$E$7="Yes"), 1, MAX((COUNTIF($D$2:$D$7, "< "&$D$2:$D$7)+1)*2))*(COUNTIF($D$2:$D$7, "<"&$D$2:$D$7)+1)), COUNTIF($D$2:$D$7, "<"&$D$2:$D$7)+1, 0))Download excel *.xls file

unique-list-sorted-alphabetically-with-a-condition.xls

Excellent, that works like a charm. Well, almost... It turns out that the list could contain both text and numbers. If the list contains a number, this formula no longer works correctly.

I saw your separate post about sorting text and numbers http://www.get-digital-help.com/2009/06/06/sorting-numbers-and-text-cells-also-removing-blanks-using-array-formula-in-excel/ but it is not clear to me how to add the condition *($E$2:$E$7="Yes") to it.

As before, any guidance is greatly appreciated!

Glen,

read this post: Create a unique distinct sorted list containing both numbers text removing blanks with a condition

Brilliant, Oscar! That works perfectly.

Thanks for the help.

Why won't this work for a named range that is on another sheet? Sorry, newbie here.

Lauren,

If you are talking about this formula:

=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))

I does work but you have to enter it as an array formula.

1. Select a cell

2. Click on formula bar

3. Copy and Paste array formula in formula bar

4. Press and hold Ctrl + Shift

5. Press Enter

Hi Oscar, Great Formula!!

Just wondering, how can the original formula you used in the article be further expanded to account for a criteria. I'll explain further.

Using your example,

Column A Column B

List Weight

DD 1

EE 0

FF 0

EE 1

GG 0

BB 1

FF 1

GG 0

DD 1

DD 0

FF 1

AA 0

EE 0

How can I then create a unique list of Column A - say in column D - for those with 0 in column B, and then another unique (i.e. no repition of AA, BB) list of Column A - say in column E - for those with 1 in column B?

I've tried to manipulate the original formula you created, but failed. Would greatly appreciate your help. :)

Thanks :)

Peter

Peter,

read this:

Create a unique distinct alphabetically sorted list with criteria

Thanks. Perfect!!

Actually, I have a further idea, Say we had a third weight (say 2) and we now wanted a list of 1 and 0 together as one list, and 2 as a separate list. How would the formula be then? Because:

(...) *($B$2:$B$14=1)*($B$2:$B$14=0)* (...) Wouldn't work.

Thanks

Peter

Peter,

Array formula in cell D6:

=INDEX($A$2:$A$16, MATCH(MIN(IF((COUNTIF($D$5:D5, $A$2:$A$16)=0)*(COUNTIF($D$2:$D$3,$B$2:$B$16)), 1, MAX((COUNTIF($A$2:$A$16, "< "&$A$2:$A$16)+1)*2))*(COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1)), IF(COUNTIF($D$2:$D$3,$B$2:$B$16), COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1,""), 0))

Array formula in cell E6:

=INDEX($A$2:$A$16, MATCH(MIN(IF((COUNTIF($E$5:E5, $A$2:$A$16)=0)*(COUNTIF($E$2,$B$2:$B$16)), 1, MAX((COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1)*2))*(COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1)), IF(COUNTIF($E$2,$B$2:$B$16),COUNTIF($A$2:$A$16, "<"&$A$2:$A$16)+1,""), 0))

Download excel *.xlsx file

Create-a-unique-distinct-alphabetically-sorted-list-with-criteria-v2.xlsx

[...] http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from... [...]

[...] Look here: Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital ... [...]

I was trying to use your technique, could I use an entire column instead of a Range? Range is fine actually I tried to rename it Destinations, but my real problem maybe I want the Range to be on Worksheet and the summary of unique values, they don't even need to be sorted on another Worksheet. I get a N/A when I try it. I try modifying the formual to start in Column A in cell 2 when making the list, the downloaded example works so I may peck away at that.

Suggestions?

Personally, I use the Google spreadsheets all the time for uniques, sorting, filtering and queries. It's so much better than Excel for that.

The simplest method in Excel is to

Highlight a range of data (make sure they all have a heading)

Go to Insert tab

Select Pivot Table

Choose new or existing worksheet, your choice

Click OK

In the PivotTable Field List find your column you want to sort

Drag that into the Row box at the bottom

You can now sort the list alphabetically if you want from the Row Labels Filter button.

You can go to Pivot Table options to remove headings, sorting options, auto width, subtotals and other stuff.

That's it.

VisicalcVeteran,

Personally, I use the Google spreadsheets all the time for uniques, sorting, filtering and queries. It's so much better than Excel for that.Competition is great!

The simplest method in Excel is toHighlight a range of data (make sure they all have a heading)

Go to Insert tab

Select Pivot Table

Choose new or existing worksheet, your choice

Click OK

In the PivotTable Field List find your column you want to sort

Drag that into the Row box at the bottom

You can now sort the list alphabetically if you want from the Row Labels Filter button.

You can go to Pivot Table options to remove headings, sorting options, auto width, subtotals and other stuff.

That's it.

A lot of steps if you have to repeat it many times.

An array formula and a named range is a lot quicker, in my opinion.

Yo!

Very good formula you have here, but very complicated and useful for few list only (hang on 500+ data). I have here my own and simple way, yet effective. I been using this for 3 years. Using only countif, vlookup and rank.

DATA is on Column C.

Formula to get unique values is on Column B

Formula to sort the unique values on Column A

The unique list in on column D.

A2=if($b2="zzz","",countif($b$2:$b$6,"<="&$b2))

B2=if(countif($c$1:$c2,$c2)=1,$c2,"zzz")

D2=iferror(vlookup(rows($d$2:$d2),$a:$c,2,0),"")

Row Column A Column B Column C Column D

1 Sort get Unique Data Unique list

2 2 mark mark ann

3 zzz mark mark

4 3 zack zack zack

5 1 ann ann

6 zzz zack

Hope this helps you as it did to me. I been using this to my 10,000 rows of name and get the sorted unique names.

mark

[…] Here is a possible solution. I found this amazing formula for unique values online at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital … Note that the formula uses a few named ranges for simplicity, make sure you add those, If you want […]

Hi, It is amazing formual and it is working. However, despite trying hard, I fail to understand what the MATCH formula exactly doing in this. I am newbie for such complicated formulas.

So, Please explain in workds what following formula is doing.

[…] at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital … or filter -> advanced filter -> unique […]

[…] I can summarize my product data with a sumifs statement. I can get unique customer data from multiple from the method described here: unique distinct value […]

Hello,

In your 'Create a unique distinct alphabetically sorted list with criteria' which is fantastic by the way, I need to extend this to have a third column 'c' with its own criteria and a column 'f' which lists those indicated in 'c'.

To clarify:

column 'e' would be a sorted list from 'a' depending on 'b'

column 'f' would be a sorted list from 'a' depending on 'c'

Try as I might, I cannot seem to get the formula correct to do this, your help would be really appreciated.

Thank you

Tony