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

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,516
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
 
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.

No issues dear

Thanks for you time....

Regards,

Humayun
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Take a look at this link.
 
Upvote 0
Take a look at this link.
Thank. I will look into it
 
Upvote 0
What about ?


Book1
ABCDEFGHIJKLMNOPQRST
1NamesSorted ListNamesSorted ListNamesSorted ListNamesSorted ListNamesSorted ListNamesSorted ListNamesSorted List
2CharlieAlphaAlphaCharlieAlphaAlphaAlphaAlphaCharlieAlpha
3BravoBravoBravoBravoBravoBravoBravoBravoBravoBravo
4AlphaCharlieAlphaCharlieAlphaCharlieAlphaCharlieAlphaFluffAlpha AlphaCharlie
5BravoFluffBravoFluffBravoFluffBravoFluffBravoHumayunBravo Charlie 
6HumayunHumayunHumayunHumayunHumayunHumayunHumayunHumayun Alpha  
7Fluff Fluff Fluff Fluff  Bravo  
8Fluff Fluff Fluff Fluff Fluff   
9Humayun Humayun Humayun Humayun Humayun   
10Charlie Charlie Charlie Charlie   
11FullFirst cellSecond cellFirst TwoTop and midTop and BottomBottom
Sheet2
Cell Formulas
RangeFormula
N2:N9N2=IFERROR(INDEX($M$2:$M$10,MATCH(1,(COUNTIF($M$2:$M$10,"<"&$M$2:$M$10)+($M$2:$M$10<>""))-SUM(COUNTIF($M$2:$M$10,N$1:N1)),0)),"")
B2:B10B2=IFERROR(INDEX($A$2:$A$10,MATCH(1,(COUNTIF($A$2:$A$10,"<"&$A$2:$A$10)+($A$2:$A$10<>""))-SUM(COUNTIF($A$2:$A$10,B$1:B1)),0)),"")
E2:E10E2=IFERROR(INDEX($D$2:$D$10,MATCH(1,(COUNTIF($D$2:$D$10,"<"&$D$2:$D$10)+($D$2:$D$10<>""))-SUM(COUNTIF($D$2:$D$10,E$1:E1)),0)),"")
H2:H10H2=IFERROR(INDEX($G$2:$G$10,MATCH(1,(COUNTIF($G$2:$G$10,"<"&$G$2:$G$10)+($G$2:$G$10<>""))-SUM(COUNTIF($G$2:$G$10,H$1:H1)),0)),"")
K2:K10K2=IFERROR(INDEX($J$2:$J$10,MATCH(1,(COUNTIF($J$2:$J$10,"<"&$J$2:$J$10)+($J$2:$J$10<>""))-SUM(COUNTIF($J$2:$J$10,K$1:K1)),0)),"")
Q2:Q10Q2=IFERROR(INDEX($P$2:$P$10,MATCH(1,(COUNTIF($P$2:$P$10,"<"&$P$2:$P$10)+($P$2:$P$10<>""))-SUM(COUNTIF($P$2:$P$10,Q$1:Q1)),0)),"")
T2:T10T2=IFERROR(INDEX($S$2:$S$10,MATCH(1,(COUNTIF($S$2:$S$10,"<"&$S$2:$S$10)+($S$2:$S$10<>""))-SUM(COUNTIF($S$2:$S$10,T$1:T1)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
What about ?


Book1
ABCDEFGHIJKLMNOPQRST
1NamesSorted ListNamesSorted ListNamesSorted ListNamesSorted ListNamesSorted ListNamesSorted ListNamesSorted List
2CharlieAlphaAlphaCharlieAlphaAlphaAlphaAlphaCharlieAlpha
3BravoBravoBravoBravoBravoBravoBravoBravoBravoBravo
4AlphaCharlieAlphaCharlieAlphaCharlieAlphaCharlieAlphaFluffAlpha AlphaCharlie
5BravoFluffBravoFluffBravoFluffBravoFluffBravoHumayunBravo Charlie 
6HumayunHumayunHumayunHumayunHumayunHumayunHumayunHumayun Alpha  
7Fluff Fluff Fluff Fluff  Bravo  
8Fluff Fluff Fluff Fluff Fluff   
9Humayun Humayun Humayun Humayun Humayun   
10Charlie Charlie Charlie Charlie   
11FullFirst cellSecond cellFirst TwoTop and midTop and BottomBottom
Sheet2
Cell Formulas
RangeFormula
N2:N9N2=IFERROR(INDEX($M$2:$M$10,MATCH(1,(COUNTIF($M$2:$M$10,"<"&$M$2:$M$10)+($M$2:$M$10<>""))-SUM(COUNTIF($M$2:$M$10,N$1:N1)),0)),"")
B2:B10B2=IFERROR(INDEX($A$2:$A$10,MATCH(1,(COUNTIF($A$2:$A$10,"<"&$A$2:$A$10)+($A$2:$A$10<>""))-SUM(COUNTIF($A$2:$A$10,B$1:B1)),0)),"")
E2:E10E2=IFERROR(INDEX($D$2:$D$10,MATCH(1,(COUNTIF($D$2:$D$10,"<"&$D$2:$D$10)+($D$2:$D$10<>""))-SUM(COUNTIF($D$2:$D$10,E$1:E1)),0)),"")
H2:H10H2=IFERROR(INDEX($G$2:$G$10,MATCH(1,(COUNTIF($G$2:$G$10,"<"&$G$2:$G$10)+($G$2:$G$10<>""))-SUM(COUNTIF($G$2:$G$10,H$1:H1)),0)),"")
K2:K10K2=IFERROR(INDEX($J$2:$J$10,MATCH(1,(COUNTIF($J$2:$J$10,"<"&$J$2:$J$10)+($J$2:$J$10<>""))-SUM(COUNTIF($J$2:$J$10,K$1:K1)),0)),"")
Q2:Q10Q2=IFERROR(INDEX($P$2:$P$10,MATCH(1,(COUNTIF($P$2:$P$10,"<"&$P$2:$P$10)+($P$2:$P$10<>""))-SUM(COUNTIF($P$2:$P$10,Q$1:Q1)),0)),"")
T2:T10T2=IFERROR(INDEX($S$2:$S$10,MATCH(1,(COUNTIF($S$2:$S$10,"<"&$S$2:$S$10)+($S$2:$S$10<>""))-SUM(COUNTIF($S$2:$S$10,T$1:T1)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Dossfm0q,

That did the trick.... Bingo

Your formula is doing what exactly what I wanted.......

And yes thanks for the detailed reply testing all possibilities

Regards,

Humayun
 
Upvote 0
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
How about? if data is text

Book1
ABC
1NamesUniqueSorted List
2Charlie5Alpha
3BravoBravo
4AlphaCharlie
5BravoFluff
6HumayunHumayun
7Fluff 
8Fluff 
9Humayun 
10Charlie 
Text
Cell Formulas
RangeFormula
B2B2=SUM(IF(FREQUENCY(IF($A$2:$A$10<>"",MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0)),ROW($A$2:$A$10)-ROW($A$2)+1),1))
C2:C10C2=IF(ROWS(C$2:C2)>$B$2,"",INDEX($A$2:$A$10,MATCH(0,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10)-SUM(COUNTIF($A$2:$A$10,$C$1:C1)),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.




For the mixed data ( i.e. numeric or text )

Book1
ABC
1NamesUniqueSorted List
2Charlie5Alpha
3BravoBravo
4AlphaCharlie
5BravoFluff
6HumayunHumayun
7Fluff 
8Fluff 
9Humayun 
10Charlie 
Mixed
Cell Formulas
RangeFormula
B2B2=COUNT(MATCH(ROW(Data)-ROW($A$2)+1,MMULT((Data<>"")*(Data>=TRANSPOSE(Data)),1^ROW(Data)),0))
C2:C10C2=IF(ROWS(C$2:C2)>$B$2,"",INDEX(Data,INDEX(MODE.MULT(IFNA(MATCH(ROW(Data)-ROW($A$2)+1,MMULT((Data<>"")*(Data>=TRANSPOSE(Data)),1^ROW(Data)),{0,0}),"")),ROWS(C$2:C2))))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Data=Mixed!$A$2:$A$10B2, C2:C10
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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