How to extract a unique distinct list of a column in excel
Table of Contents
- How to extract unique distinct values of a column
- How to extract unique values of a column
- How to extract unique distinct values of a column using advanced filter
Unique distinct values are all cell values but duplicate values are merged into one distinct value.
Edit 2009-06-29
Formula in cell B2:
=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))+ CTRL + SHIFT + ENTER and copy it down as far as necessary.
Thanks, Eero!
Excel 2007 users can remove errors using iferror() function:
=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"") + CTRL + SHIFT + ENTER and copy it down as far as necessary.
The formula is an array formula. To create an array formula you press Ctrl + Shift + Enter after you have entered the formula.
Excel 2003 users can remove errors using isna() function:
=IF(ISNA(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))), "", INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))) + CTRL + SHIFT + ENTER and copy it down as far as needed.
How this array formula works
=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))
The array formula uses MATCH() to find the first 0 (zero) value in the countif array. When you copy the array formula down the countif formula range expands. The first cell: COUNTIF($B$1:B1,List), the second cell: COUNTIF($B$1:B2,List)
and so on.
See picture below. Click for a larger image.
How to create a unique list
Unique values are values existing only once in a list.
Array formula in C2:
=INDEX(List, MATCH(0, COUNTIF(C1:$C$1, List)+(COUNTIF(List, List)<>1), 0)) + CTRL + SHIFT + ENTER and copy the formula down as far as needed.
How to customize the formula to your excel spreadsheet
Change the named ranges. If your unique list starts at F3, change $B$1:B1 or C1:$C$1 in the above formula to $F$2:F2
Named ranges
List (A2:A20)
What is named ranges?
Download excel sample file for this tutorial.
Extract-a-unique-distinct-list-in-excel.xls
(Excel 97-2003 Workbook *.xls)
Read more related articles in the archive.
To extract duplicates, see this post: Extract a list of duplicates from a column using array formula in excel
How to extract unique distinct values of a column using advanced filter
Unique distinct values are all cell values but duplicate values are merged into one distinct value.
- Click "Data" in the menu
- Click "Advanced Filter" button on the ribbon
- Click "Copy to another location"
- Click "List range:" and select range to filter unqiue distinct values
- Click "Copy to: and select a range
- Click "Unique records only"
- Click "OK"!
Functions in this article:
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
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
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
INDIRECT(ref_text,[a1])
Returns the reference specified by a text string
ROW(reference) returns the rownumber of a reference
SUM(number1,[number2],)
Adds all the numbers in a range of cells
LARGE(array,k) returns the k-th largest row number in this data set.
This blog article is one out of thirteen articles on the same subject "unique".
- How to extract a unique distinct list from a column in excel
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Extract distinct unique sorted year and month list from a date series in excel
- Create a unique distinct list from a date range in excel
- Unique values from multiple columns using array formulas
- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Filter unique distinct values from two ranges combined in excel 2007
- Create a unique list and sort by occurrances from large to small
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria
copy-of-how-to-extract-a-unique-list-in-excel.xls
Related posts:
- Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
- Extract a unique distinct list sorted from A-Z from range in excel
- Unique distinct list from a column sorted A to Z using array formula in excel
- Extract a unique distinct list from three columns in excel
- Unique distinct list sorted based on occurrance in a column in excel
- Extract a unique distinct list from two columns using excel 2007 array formula
- Create unique distinct list from column where an adjacent column meets criteria
- Extract distinct unique sorted year and month list from a date series in excel
- Create a unique distinct alphabetically sorted list, extracted from a column in excel
- Unique list to be created from a column where an adjacent column has text cell values
April 10th, 2009 at 4:49 pm
Thanks a lot!!! You've made my day!
April 25th, 2009 at 8:57 am
Hey there - thanks a lot for this tip - though I initially could not get this to work - eventually I figured out MY problem the INDEX rownum was using "ROW(A2:A20)-1", when it should be using "ROW()-1" - otherwise it ALWAYS returns the start of the array.
April 26th, 2009 at 10:37 am
Thank you for visiting Get Digital Help and commenting! I have changed the formula and the attached file. Using named ranges simplifies formula customization a lot!
May 21st, 2009 at 5:38 am
Many thanks for the useful information posted on this website! However, as I am working on a very large set of data (from between 50 - 8000 records) and whenever I change the name range to go beyond the 1000th record, the unique list value would return error. I have tried to name the whole column A but it also doesnt work.
would be grateful if you could assist on this.
Many thanks for all your effort!
May 21st, 2009 at 9:15 am
Thank you!
I tried to recreate your error, but failed. I have attached an excel file with over a 1000 records that works for me. The excel file is at the end of the above blog post. Try it and see if the excel file returns an error.
I am using excel 2007, what version are you using?
Have you changed cell ranges for both named ranges in your excel sheet?
List (A2:A20)
unique_start (B2)
Maybe you have a blank cell somewhere in your list? A blank cell returns an error. See this post:
http://www.get-digital-help.com/2009/03/10/how-to-automatically-create-a-unique-list-and-remove-blanks/
June 13th, 2009 at 1:57 pm
There is a somewhat shorter approach:
Let initial items in colA be named as "List". In B2 enter the
following array-formula:
=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) and copy it down as far as necessary, to get unique list.
June 13th, 2009 at 10:06 pm
That is really amazing, you made the array formula even shorter!
To remove any errors, Excel 2007 users can use IFFERROR() function:
=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"") + CTRL + SHIFT + ENTER and copy it down as far as necessary.
Thank you for your valueable comment!
June 25th, 2009 at 2:10 am
hi.
thanks for the formulas (eero's and the revised one) guys. it really cured my headaches.
for the unique list generated (Column B), can the...
1) unique list sorted A-Z order?
2) unique list sorted based on occurrence (highest to lowest)?
the trick is: can the sorting be done without complicating eero's formula above?
reason i'm asking because the other posts on sorting is too complicated for my head to wrap around
thx!
June 27th, 2009 at 8:04 am
Interesting questions!
I´ll see if I can come up with some good solutions.
Thanks for commenting!
June 28th, 2009 at 4:19 am
thanks!
in case you're wondering, eero's formula does not need the unique list range (Column B) to be same size as the main list (Column A).
I just need to copy down the formula to get the rest of the unique list.
Your other posts on sorting is based on range-based array formula (i.e. Column B must be same size as Column A in order for the array formula to work). This works well if you have short list, but if u have thousands of entries, unique list range must equal the same.
One other that thing that i noticed is the ranged-based array formula is fixed (you can't delete those 'empty' cells in this range).
anyways, thanks again for looking into another solutions and not to forget your valuable blogs here!
June 28th, 2009 at 9:29 pm
David, here is the answer to your first question.
unique list sorted A-Z order?
Array formula in cell B2, see picture above.
=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)) + CTRL + SHIFT + ENTER and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z.
If anyone can come up with a shorter formula, i would be happy!
June 29th, 2009 at 1:39 am
oh wow! it works!
THANKS!
June 29th, 2009 at 10:47 pm
David, here is the answer to your second question.
unique list sorted based on occurrence (highest to lowest)?
Array formula in cell B2, see picture above.
=INDEX(List, MATCH(IF(MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))=0, 1, MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))), COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1), 0))+ CTRL + SHIFT + ENTER and copy it down as far as necessary, to get sorted based on occurrence (highest to lowest)
I have to think some more to get this formula shorter, I don´t like the length of it.
June 30th, 2009 at 3:15 am
works great!
thanks!
p/s: as long as the array formula works, i'm happy. but do keep up your great job on the formulas! MS Excel gurus should visit and give you a thumbs up!
July 30th, 2009 at 3:20 pm
Wow the short formula works, amazing!!!
Thanks!
December 16th, 2009 at 1:04 am
Wow! This is just amazing. Works great. I would love if someone could explain how this works step by step. I tried, but couldn't. I am trying to extend this fomula with a user defined formula.
December 17th, 2009 at 5:43 am
[...] How to extract a unique distinct list from a column in excel [...]
December 17th, 2009 at 5:07 pm
Sriram Venkitachalam,
I now have tried to explain the array formula, see the new section above.
Thanks for commenting!
January 21st, 2010 at 9:05 am
Would you please advise me on how to created named range which includes A-Z sorted list of UNIQUE values of other list?
January 29th, 2010 at 10:50 am
Thanks a lot for this example. But is there also a way without using CSE? because a use a tool where i import Excel file which does not support CSE formulas in Excel.
Hopefuly someone has an idea
January 31st, 2010 at 6:11 am
Dear All, It's my first post right here, and I'd like to thank you all with special THANKS for ADMIN. Well, I'm looking for more info and explanation about the current topic of http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/ It has mentioned above,
"How this array formula works: =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) The array formula uses MATCH() to find the first 0 (zero) value in the COUNTIF array. When you copy the array formula down the COUNTIF formula range expands. The first cell: COUNTIF($B$1:B1,List), the second cell: COUNTIF($B$1:B2,List) and so on"
It's understood, but I'm still puzzled for the following reasons:
1. When applying the mentioned formula, the result is always the first record only.
2. All array elements shows INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) in formula bar, which indicates that the range expansion mentioned above is not applicable.
3. The attached example sheet shows
@ cell B2: INDEX(List,MATCH(0,COUNTIF(B1:$B$1,List),0)),
while @ cell B3: INDEX(List,MATCH(0,COUNTIF(B$1:$B2,List),0)),
and It’s OK, if we are talking about singular cell formula.
So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?
Do I miss something?
Is there any Excel settings should be takes place?
Please help & advise.
Thank YOU & BEST Regards,
January 31st, 2010 at 6:29 am
Raymond,
You need to convert your formulas to values.
Copy and "Paste Special.." the values created by the CSE formula.
Select "Values" and click OK.
OnlineAlone,
Copy =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) + CTRL + SHIFT + ENTER into your first cell. (Cell B2)
Copy your first cell to the remaining cells.
The formula then automatically changes to:
Cell B3: =INDEX(List,MATCH(0,COUNTIF($B$1:B2,List),0))
Cell B4: =INDEX(List,MATCH(0,COUNTIF($B$1:B3,List),0))
and so on.
January 31st, 2010 at 6:49 am
Oscar,
Thank you for your quick response.
OK, I got it.
January 31st, 2010 at 10:16 pm
OnlineAlone,
I missed this question:
So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?
You have found an error in the attached file.
I have now changed the formula in the attached file and uploaded it to this blog post again.
Here is an excellent explanation of absolute and relative references:
http://www.cpearson.com/excel/relative.aspx
February 9th, 2010 at 10:34 am
hi all,
thanks for the great formula/array formula. it works great.
lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product).
e.g.
1-Jan-2010 ProductA
1-Jan-2010 ProductB
1-Jan-2010 ProductC
1-Jan-2010 ProductD
2-Jan-2010 ProductA
2-Jan-2010 ProductB
2-Jan-2010 ProductC
...
...
the unique distinct values from the dates are 1-Jan-2010, 2-Jan-2010, and so on.
In a month (31-days) x 13 products = 403 entries. Multiply this to 12-months = 403 x 12 months ~est. 4.9k dates to be parsed.
the jackpot question: Is there a way to easily parse long list, without sacrificing performance (recalculations)?
thx!
February 9th, 2010 at 6:38 pm
David,
have you tried "Advanced Filter"?
http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#advfilter
Thanks for commenting!
February 10th, 2010 at 1:04 am
2 problems i see using Advanced Filter:
a) The first 2 generated entry is a repeat. i.e. 1-Jan-2010, 1-Jan-2010, 2-Jan-2010, 3-Jan-2010, and so on.
first entry is named “Extract”).
b) The dates from my master table is set with a dynamic named range. Advanced Filter will generated unique
distinct list, manually.
I daily update the table
thx!
February 10th, 2010 at 11:05 pm
david,
See this blog post (and the attached file):
http://www.get-digital-help.com/2010/02/10/create-a-unique-distinct-list-of-a-long-list-without-sacrificing-performance-using-vba-in-excel/
February 11th, 2010 at 1:25 am
just noticed this standalone post. incredible.
will try it out.
note: reason why Advance Filter is not preferred (by me anyway) is the list will gets longer daily. i wouldn't want to keep (manually) using Advanced Filter.
'sides, i'm preparing the file for Excel noobs. he/she should not be complicated by the steps
the VBA macro button is interesting tho! will try it now!!
thx guys!!
February 25th, 2010 at 1:11 am
I been trying to do this for a long time. I tried by pivotTable to extract the list without duplicate items.
This much easier, clear, and great! Thanks a lot!
I found out the column 'A' must choose the title of it 'A1' also, to avoid in column 'B' shows 'A2' twice, and there is no problem to choose 'A1' coz it shows the same title in 'B1'.
Thanks again!
March 9th, 2010 at 1:01 pm
In excel 2003 and older you can remove error by using ISNA() function.
The formula is brilliant but it took me some significant amount of time to understand it (use Evaluate formula).
March 11th, 2010 at 9:37 pm
Wiciu,
Thanks!! I have added the ISNA() function to this post.
/Oscar