How to make a unique List with Dynamic Formula and sort it from descending to ascending?

L

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
163
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

Can you help - how to make a unique List with Dynamic Formula and sort it from descending to ascending at the same time?

A1

294 Lx - Amadora (MÁgua - Sul)
297 Lx - Amadora (Alfragide, ÁLivres)
294 Lx - Amadora (MÁgua - Sul)
296 Lx - Amadora (Venteira)
294 Lx - Amadora (MÁgua - Sul)
294 Lx - Amadora (MÁgua - Sul)
294 Lx - Amadora (MÁgua - Sul)
294 Lx - Amadora (MÁgua - Sul)
295 Lx - Amadora (MÁgua - Norte)
295 Lx - Amadora (MÁgua - Norte)
295 Lx - Amadora (MÁgua - Norte)
295 Lx - Amadora (MÁgua - Norte)
296 Lx - Amadora (Venteira)
296 Lx - Amadora (Venteira)
296 Lx - Amadora (Venteira)
294 Lx - Amadora (MÁgua - Sul)
297 Lx - Amadora (Alfragide, ÁLivres)
297 Lx - Amadora (Alfragide, ÁLivres)
295 Lx - Amadora (MÁgua - Norte)

Thank you very much.
 

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.
Sort by date Sort by votes
Doea this work for you:
Book1
AB
1294 Lx - Amadora (MÁgua - Sul)297 Lx - Amadora (Alfragide, ÁLivres)
2297 Lx - Amadora (Alfragide, ÁLivres)296 Lx - Amadora (Venteira)
3294 Lx - Amadora (MÁgua - Sul)295 Lx - Amadora (MÁgua - Norte)
4296 Lx - Amadora (Venteira)294 Lx - Amadora (MÁgua - Sul)
5294 Lx - Amadora (MÁgua - Sul)
6294 Lx - Amadora (MÁgua - Sul)
7294 Lx - Amadora (MÁgua - Sul)
8294 Lx - Amadora (MÁgua - Sul)
9295 Lx - Amadora (MÁgua - Norte)
10295 Lx - Amadora (MÁgua - Norte)
11295 Lx - Amadora (MÁgua - Norte)
12295 Lx - Amadora (MÁgua - Norte)
13296 Lx - Amadora (Venteira)
14296 Lx - Amadora (Venteira)
15296 Lx - Amadora (Venteira)
16294 Lx - Amadora (MÁgua - Sul)
17297 Lx - Amadora (Alfragide, ÁLivres)
18297 Lx - Amadora (Alfragide, ÁLivres)
19295 Lx - Amadora (MÁgua - Norte)
Sheet1
Cell Formulas
RangeFormula
B1B1=SORT(UNIQUE(A1:A19),1,-1)
 
Upvote 0
If you don't have the new Excel 365 functions (even if you do have 365 you might not have them as they haven't been rolled out to all users yet.... it has only just been added to mine)….

Lacan said:
and sort it from descending to ascending at the same time?
Click to expand...
Not sure by this statement if you want ascending or descending. The below is for Ascending (I am not sure if I can work out a formula for Descending with regular formula), it needs array entering with Ctrl-Shift + Enter. Also not sure by your image what cells the data is in (it would be a good idea for future posts to look at using the boards XL2BB app).
Book1
AB
1Column1
2294 Lx - Amadora (MÃgua - Sul)294 Lx - Amadora (MÃgua - Sul)
3297 Lx - Amadora (Alfragide, ÃLivres)295 Lx - Amadora (MÃgua - Norte)
4294 Lx - Amadora (MÃgua - Sul)296 Lx - Amadora (Venteira)
5296 Lx - Amadora (Venteira)297 Lx - Amadora (Alfragide, ÃLivres)
6294 Lx - Amadora (MÃgua - Sul) 
7294 Lx - Amadora (MÃgua - Sul) 
8294 Lx - Amadora (MÃgua - Sul) 
9294 Lx - Amadora (MÃgua - Sul) 
10295 Lx - Amadora (MÃgua - Norte) 
11295 Lx - Amadora (MÃgua - Norte) 
12295 Lx - Amadora (MÃgua - Norte) 
13295 Lx - Amadora (MÃgua - Norte) 
14296 Lx - Amadora (Venteira) 
15296 Lx - Amadora (Venteira) 
16296 Lx - Amadora (Venteira) 
17294 Lx - Amadora (MÃgua - Sul) 
18297 Lx - Amadora (Alfragide, ÃLivres) 
19297 Lx - Amadora (Alfragide, ÃLivres) 
20295 Lx - Amadora (MÃgua - Norte) 
Sheet5
Cell Formulas
RangeFormula
B2:B20B2=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($A$2:$A$20,"<"&$A$2:$A$20)-SUM(COUNTIF($A$2:$A$20,"="&B$1:B1)),0)),"")
 
Last edited:
Upvote 0
The formula in column C below seems to work for Descending (needs array entering with Ctrl-Shift + Enter)...
Book1
ABC
1Column1
2294 Lx - Amadora (MÃgua - Sul)294 Lx - Amadora (MÃgua - Sul)297 Lx - Amadora (Alfragide, ÃLivres)
3297 Lx - Amadora (Alfragide, ÃLivres)295 Lx - Amadora (MÃgua - Norte)296 Lx - Amadora (Venteira)
4294 Lx - Amadora (MÃgua - Sul)296 Lx - Amadora (Venteira)295 Lx - Amadora (MÃgua - Norte)
5296 Lx - Amadora (Venteira)297 Lx - Amadora (Alfragide, ÃLivres)294 Lx - Amadora (MÃgua - Sul)
6294 Lx - Amadora (MÃgua - Sul)  
7294 Lx - Amadora (MÃgua - Sul)  
8294 Lx - Amadora (MÃgua - Sul)  
9294 Lx - Amadora (MÃgua - Sul)  
10295 Lx - Amadora (MÃgua - Norte)  
11295 Lx - Amadora (MÃgua - Norte)  
12295 Lx - Amadora (MÃgua - Norte)  
13295 Lx - Amadora (MÃgua - Norte)  
14296 Lx - Amadora (Venteira)  
15296 Lx - Amadora (Venteira)  
16296 Lx - Amadora (Venteira)  
17294 Lx - Amadora (MÃgua - Sul)  
18297 Lx - Amadora (Alfragide, ÃLivres)  
19297 Lx - Amadora (Alfragide, ÃLivres)  
20295 Lx - Amadora (MÃgua - Norte)  
Sheet5
Cell Formulas
RangeFormula
B2:B20B2=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($A$2:$A$20,"<"&$A$2:$A$20)-SUM(COUNTIF($A$2:$A$20,"="&B$1:B1)),0)),"")
C2:C20C2=IFERROR(INDEX($A$2:$A$20, MATCH(LARGE(IF(COUNTIF($C$1:C1, $A$2:$A$20)=0, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), 1), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), 0)),"")
 
Last edited:
Upvote 0
KevCarter said:
Please try:

=SORT(UNIQUE(A3:A20),,-1)
Click to expand...
J.Ty. said:
Doea this work for you:
Book1
AB
1294 Lx - Amadora (MÁgua - Sul)297 Lx - Amadora (Alfragide, ÁLivres)
2297 Lx - Amadora (Alfragide, ÁLivres)296 Lx - Amadora (Venteira)
3294 Lx - Amadora (MÁgua - Sul)295 Lx - Amadora (MÁgua - Norte)
4296 Lx - Amadora (Venteira)294 Lx - Amadora (MÁgua - Sul)
5294 Lx - Amadora (MÁgua - Sul)
6294 Lx - Amadora (MÁgua - Sul)
7294 Lx - Amadora (MÁgua - Sul)
8294 Lx - Amadora (MÁgua - Sul)
9295 Lx - Amadora (MÁgua - Norte)
10295 Lx - Amadora (MÁgua - Norte)
11295 Lx - Amadora (MÁgua - Norte)
12295 Lx - Amadora (MÁgua - Norte)
13296 Lx - Amadora (Venteira)
14296 Lx - Amadora (Venteira)
15296 Lx - Amadora (Venteira)
16294 Lx - Amadora (MÁgua - Sul)
17297 Lx - Amadora (Alfragide, ÁLivres)
18297 Lx - Amadora (Alfragide, ÁLivres)
19295 Lx - Amadora (MÁgua - Norte)
Sheet1
Cell Formulas
RangeFormula
B1B1=SORT(UNIQUE(A1:A19),1,-1)
Click to expand...


Works Wonderfull.

Thank you very much!
 
Upvote 0
MARK858 said:
The formula in column C below seems to work for Descending (needs array entering with Ctrl-Shift + Enter)...
Book1
ABC
1Column1
2294 Lx - Amadora (MÃgua - Sul)294 Lx - Amadora (MÃgua - Sul)297 Lx - Amadora (Alfragide, ÃLivres)
3297 Lx - Amadora (Alfragide, ÃLivres)295 Lx - Amadora (MÃgua - Norte)296 Lx - Amadora (Venteira)
4294 Lx - Amadora (MÃgua - Sul)296 Lx - Amadora (Venteira)295 Lx - Amadora (MÃgua - Norte)
5296 Lx - Amadora (Venteira)297 Lx - Amadora (Alfragide, ÃLivres)294 Lx - Amadora (MÃgua - Sul)
6294 Lx - Amadora (MÃgua - Sul)  
7294 Lx - Amadora (MÃgua - Sul)  
8294 Lx - Amadora (MÃgua - Sul)  
9294 Lx - Amadora (MÃgua - Sul)  
10295 Lx - Amadora (MÃgua - Norte)  
11295 Lx - Amadora (MÃgua - Norte)  
12295 Lx - Amadora (MÃgua - Norte)  
13295 Lx - Amadora (MÃgua - Norte)  
14296 Lx - Amadora (Venteira)  
15296 Lx - Amadora (Venteira)  
16296 Lx - Amadora (Venteira)  
17294 Lx - Amadora (MÃgua - Sul)  
18297 Lx - Amadora (Alfragide, ÃLivres)  
19297 Lx - Amadora (Alfragide, ÃLivres)  
20295 Lx - Amadora (MÃgua - Norte)  
Sheet5
Cell Formulas
RangeFormula
B2:B20B2=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($A$2:$A$20,"<"&$A$2:$A$20)-SUM(COUNTIF($A$2:$A$20,"="&B$1:B1)),0)),"")
C2:C20C2=IFERROR(INDEX($A$2:$A$20, MATCH(LARGE(IF(COUNTIF($C$1:C1, $A$2:$A$20)=0, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), 1), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), 0)),"")
Click to expand...


Works Wonderfull.

Thank you very much!
 
Upvote 0
Lacan said:
Works Wonderfull.

Thank you very much!
Click to expand...
If the formulas J.Ty. and KevCarter posted work for you then you have Excel 365 and if you are only going to use computers with 365 then you should use the formula they posted and not mine (btw if you go to your profile you can set it so it shows us what version you have (see how my and J.Ty.'s profiles appear in this thread) which makes it a lot easier to come up with the most appropriate answers for your questions)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
How to extract data using extensions Names and Complete Names ???
Replies
3
Views
349
Lacan
L
L
  • Question
Very simple and easy question: - How to use Indirect Function + Xlookup with 2 Criteria?
Replies
2
Views
309
Lacan
L
L
  • Question
How to order (sort) the extent List below from descending to ascending to put in a non sequence cells?
2 3
Replies
21
Views
1K
Lacan
L
L
  • Solved
How to use Filter Function, Unique Function and Large Function all together?
2 3
Replies
21
Views
1K
Lacan
L
L
  • Question
Make a Drop Down List ( the challenge is to create the name based on matching range to make it dropable by the indirect formula )
Replies
4
Views
386
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,165
Messages
6,123,390
Members
449,098
Latest member
ArturS75

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