Extract unique items from list > Amend the formula to give sorted results

[hrayani]

Hello Friends,

I am using the below formula to give me unique items from a list.

=IFERROR(INDEX(L$3:L$92,MATCH(0,IF(ISBLANK(L$3:L$92),1,COUNTIF(L$95:L95,L$3:L$92)),0)),"")

I want the the formula to give me sorted results > starting from lowest.

Any help would be appreciated

Regards,

Humayn

 

[AhoyNC]

Maybe this link will help.

 

[Dossfm0q]

UNIQUE function returning 0

Hi all. I am using the UNIQUE function on a column full of text and the last unique value being returned is a 0. I have this occurring in 2 different UNIQUE functions. How can I check the reference cells to see where this is coming from? Any help is greatly appreciated.

www.mrexcel.com

 

[Fluff]

@Dossfm0q
How is that thread going to help the OP? Excel 2016 does not have the Unique function.

 

[hrayani]

Hello Friends,

This did the trick

=INDEX(list,MATCH(0,COUNTIF(list,"<"&list)-SUM(COUNTIF(list,C$1:C1)),0))

But the problem begins when the first or the second cell in the list is a blank

 

[Fluff]

Can you post some sample data using the XL2Bb add-in.

 

[Dossfm0q]

what about
You Miss "="

=INDEX(List,MATCH(0,COUNTIF(List,"<="&List)-SUM(COUNTIF(List,A$1:A1)),0))

asaa.xlsm
	A	B	C	D
1		Your Formula Sort		Excel sort
2	asd	asd	=	asd
3	vbnh	asdg	=	asdg
4	sdff	asef	=	asef
5	ghfd	dff	=	dff
6	dff	ghfd	=	ghfd
7	asdg	jkm	=	jkm
8	asef	sdff	=	sdff
9	jkm	vbnh	=	vbnh
Sheet2

Cell Formulas
Range	Formula
B2:B9	B2	=INDEX(List,MATCH(0,COUNTIF(List,"<="&List)-SUM(COUNTIF(List,A$2:A2)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

@Dossfm0q
How does that address the OP's problem with blank cells?
Also your formula does not handle duplicate values, unlike the formula supplied by the OP.

 

[hrayani]

Can you post some sample data using the XL2Bb add-in.

Sure why not

Book1
	A	B	C
1	Names		Sorted List
2	Charlie		Alpha
3	Bravo		Bravo
4	Alpha		Charlie
5	Bravo		Fluff
6	Humayun		Humayun
7	Fluff
8	Fluff
9	Humayun
10	Charlie
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=IFERROR(INDEX(list,MATCH(0,COUNTIF(list,"<"&list)-SUM(COUNTIF(list,C$1:C1)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
list	=Sheet1!$A$2:$A$10	C2:C10

So when I delete cell A1 or A2 then the sorted list shows all zero in there

One more thing. The formula works for either numbers or names. Can it work for both i.e. a list containing numbers & names

Regards

 

[Fluff]

Unfortunately, I cannot figure out a formula (for your version of Excel) that will do what you want.
You might be better of using the advanced filter to get a list of distinct values & then sort that.