Unique distinct list from a column sorted A to Z
How do I create a unique distinct list from a column sorted A to Z using array formula?
Array formula in D3:
How to create an array formula
- Select cell D3.
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to copy this array formula
- Select cell D3.
- Copy (Ctrl + C) cell D2.
- Select D3:D8
- Paste (CTRL + V)
How this array formula works
Step 1 - Filter unique distinct values
=INDEX($B$3:$B$13, MATCH(MIN(IF(COUNTIF($D$2:D2, $B$3:$B$13)=0,COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1,9.9999E+307)), COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1, 0))
COUNTIF($D$1:D1, Â List)=0
becomes
COUNTIF("Unique distinct list sorted A to Z:", {"VV";"NN";"JJ";"TT";"DD";"NN";"II";"VV";"DD";"II";"JJ"})=0
becomes
({0;0;0;0;0;0;0;0;0;0;0})=0
and returns {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}.
Step 2 - Remove duplicate values from array
=INDEX($B$3:$B$13, MATCH(MIN(IF(COUNTIF($D$1:D1, $B$3:$B$13)=0,COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1,9,9999E+307)), COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1, 0))
MIN(IF(COUNTIF($D$1:D1, $B$3:$B$13)=0,COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1,9,9999E+307))
becomes
MIN(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{10;7;5;9;1;7;3;10;1;3;5},9,9999E+307))
becomes
MIN({10;7;5;9;1;7;3;10;1;3;5})
and returns 1.
Step 3 - Match smallest value
=INDEX($B$3:$B$13, MATCH(MIN(IF(COUNTIF($D$1:D1, $B$3:$B$13)=0,COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1,9,9999E+307)), COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1, 0))
MATCH(MIN(IF(COUNTIF($D$1:D1, $B$3:$B$13)=0,COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1,9,9999E+307)), COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1, 0)
becomes
MATCH(1, {10;7;5;9;1;7;3;10;1;3;5}, 0)
and returns 5.
Step 3 - Return a value or reference of the cell at the intersection of a particular row and column
=INDEX($B$3:$B$13, MATCH(MIN(IF(COUNTIF($D$1:D1, $B$3:$B$13)=0,COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1,9,9999E+307)), COUNTIF($B$3:$B$13, "<"&$B$3:$B$13)+1, 0))
becomes
=INDEX($B$3:$B$13, 5)
becomes
=INDEX({"VV";"NN";"JJ";"TT";"DD";"NN";"II";"VV";"DD";"II";"JJ"}, 5)
and returns DD in cell D2.
Get Excel *.xlsx file
Unique-distinct-list-from-a-column-sorted-A-to-Z-using-array-formula11.xlsx
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Excel categories
7 Responses to “Unique distinct list from a column sorted A to Z”
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.
Hi Oscar!
Can you help me?
I have a name products column and their prices (other column).
I want to create unique distinct list with products name sorted by SUM of prices. Is it real using array formula?
Bill,
read this post:
Filter unique distinct list sorted based on sum of adjacent values
Thanks, Oscar!
I am using the following formula. How can i get it to work if there are blank cells?
How can i get it to work if there are formulas in the column?
=IFERROR(INDEX(List1,MATCH(MIN(IF(COUNTIF($F$9:F9,List1)=0,1,MAX((COUNTIF(List1,"<"&List1)+1)*2))*(COUNTIF(List1,"<"&List1)+1)),COUNTIF(List1,"<"&List1)+1,0)),"")
Jimmie,
try this formula:
=INDEX(List, MATCH(MIN(IF((List="")+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER($A$2:$A$15), 1, 0))+1))), IF((List="")+COUNTIF(B1:$B$1, List), "", IF(ISNUMBER(List), COUNTIF(List, "<"&List), COUNTIF(List, "<"&List)+SUM(IF(ISNUMBER(List), 1, 0))+1)), 0)) Get the Excel file Unique-and-Sort-numbers-and-text-cells-using-excel-array-formula-works-with-formulas.xls
How about "Z to A"?
Thanks lots!
Thank You!