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

H

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
 
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
319
etaf
etaf
M
  • Question Question
Index Match formula pulling from a longer list with blank row spaces or unwanted data into a shorter list without row spaces
Replies
5
Views
391
Eric W
Eric W
H
  • Solved
Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula
Replies
8
Views
207
Peter_SSs
Peter_SSs
R
  • Question Question
Populate list of categories
Replies
0
Views
140
RIZVI
R
E
  • Question Question
Exel Filter formula with multiple criterias
Replies
2
Views
285
Fluff
Fluff

Forum statistics

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

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