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

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,539
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

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

Excel Formula:
=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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
 
Upvote 0
@Dossfm0q
How is that thread going to help the OP? Excel 2016 does not have the Unique function.
 
Upvote 0
Hello Friends,

This did the trick

Excel Formula:
=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
 
Upvote 0
Can you post some sample data using the XL2Bb add-in.
 
Upvote 0
what about
You Miss "="

VBA Code:
=INDEX(List,MATCH(0,COUNTIF(List,"<="&List)-SUM(COUNTIF(List,A$1:A1)),0))
asaa.xlsm
ABCD
1Your Formula SortExcel sort
2asdasd =asd
3vbnhasdg =asdg
4sdffasef =asef
5ghfddff =dff
6dffghfd =ghfd
7asdgjkm =jkm
8asefsdff =sdff
9jkmvbnh =vbnh
Sheet2
Cell Formulas
RangeFormula
B2:B9B2=INDEX(List,MATCH(0,COUNTIF(List,"<="&List)-SUM(COUNTIF(List,A$2:A2)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
@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.
 
Upvote 0
Can you post some sample data using the XL2Bb add-in.
Sure why not

Book1
ABC
1NamesSorted List
2CharlieAlpha
3BravoBravo
4AlphaCharlie
5BravoFluff
6HumayunHumayun
7Fluff 
8Fluff 
9Humayun 
10Charlie 
Sheet1
Cell Formulas
RangeFormula
C2:C10C2=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
NameRefers ToCells
list=Sheet1!$A$2:$A$10C2: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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top