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

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,502
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
 
Sort by date Sort by votes
Fluff said:
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.
Click to expand...

No issues dear

Thanks for you time....

Regards,

Humayun
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Take a look at this link.
www.youtube.com

ExcelIsFun

Over 3,300 Excel How To Videos from Beginner to Advanced, including downloadable Excel files to practice and pdf notes to summarize concepts - all free at Yo...
www.youtube.com www.youtube.com
 
Upvote 0
AhoyNC said:
Take a look at this link.
www.youtube.com

ExcelIsFun

Over 3,300 Excel How To Videos from Beginner to Advanced, including downloadable Excel files to practice and pdf notes to summarize concepts - all free at Yo...
www.youtube.com www.youtube.com
Click to expand...
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
Dossfm0q said:
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.
Click to expand...

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
hrayani said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

M
  • Solved
Combining Formulas
Replies
7
Views
176
Marhier
M
H
  • Question
Excel Formula
Replies
0
Views
140
hrayani
H
A
  • Solved
Using Index to retrieve record numbers but need list descending.
Replies
8
Views
281
Fluff
Fluff
E
  • Question
Formula not working
Replies
1
Views
97
RoryA
RoryA
J
  • Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
141
juliecooper255
J

Forum statistics

Threads
1,216,112
Messages
6,128,901
Members
449,477
Latest member
panjongshing

Share this page

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