Modifying this formula to sort from A to Z instead of Z to A

B

bearcub

Well-known Member
Joined
May 18, 2005
Messages
734
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I have the formula array formula that works great. However, I would like to modify it so it sorts from smallest to largest. Right now, it is sorting largest to smallest and not vice versa. I did trying nesting this inside the Small function but I may not have set up the Rows function properly. Or should I use the Aggregate instead? Do I have the Rows function set up properly or should I use the row function instead?

Code: 
SMALL(LOOKUP(2,1/(((COUNTIF($J$1:J2,$F$2:$F$2000)=0)*(COUNTIF($F$2:$F$2000,$F$2:$F$2000)>1))),$F$2:$F$2000),ROWS($A$1:A1))

Thank you for your help,

Michael
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
I think I can,

The first column is where I have all the members sorted smallest to largest (so i can apply the lookup formula). The second columns where I have the formula to extract the duplicates. #373 should be at the top of the list but it is the very last one in Column J

Here is a snippet from the list. I have a 156 duplicates and 373 is listed at the bottom. Instead of having 22923 (which is the last duplicate in the table) I should have 373. Some how, I have to reverse the sort order in the formula.

Code: 
ID                      	Duplicate
3				22928
4				22863
55				22772
128				22506
160				22415
169				22362
171				22301
226				22279
237				22266
290				22257
295				22221
298				22200
373				22141
373				22063
373				21766
531				21761
624				21738
629				21730
720				21722
4803				902
4816				780
4835				373

Thank you for your help,

Michael
 
Upvote 0
Assuming the IDs ate numeric, not alphanumeric...
See if this example helps - a more efficient formula


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
ID​
[/td][td][/td][td]
Duplicate​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
3​
[/td][td][/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
4​
[/td][td][/td][td]
4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
4​
[/td][td][/td][td]
10​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
3​
[/td][td][/td][td]
11​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
8​
[/td][td][/td][td]
16​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
9​
[/td][td][/td][td]
17​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
10​
[/td][td][/td][td]
19​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
11​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
10​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
11​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
15​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
16​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
16​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td]
17​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td]
17​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td]
18​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td]
19​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td]
19​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td]
19​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td]
20​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in C2 copied down
=IFERROR(INDEX(A$2:A$21,SMALL(IF(FREQUENCY(A$2:A$21,A$2:A$21)>1,ROW(A$2:A$21)-ROW(A$2)+1),ROWS(C$2:C2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Upvote 0
Another option, if it's numeric data:

FGHIJ
List

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]66[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=IF(MAX($F$2:$F$2000)=MAX($J$2:$J2),"",SMALL($F$2:$F$2000,COUNTIF($F$2:$F$2000,"<="&J2)+1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This one is not an array formula.

Incidentally, your original formula sorted in reverse order since the LOOKUP finds the last matching entry. The SMALL didn't help since it was only looking at the 1 entry LOOKUP returned.
 
Upvote 0
Thank you Eric, this will definitely give me a unique list.
 
Upvote 0
Sorry for the confusion in the post. I appreciate your formula, I will add it to my arsenal when I need to do something like that (which happens quite often).

what I wanted to do initially was to use the Lookup formula - which creates a unique list - but sort it from smallest to largest. As it is written now, it extracts the unique items and sorts it from largest to smallest.

Thank you for your help

Michael
 
Upvote 0
Thank you Marcelo for the clarification. It works like a charm, thank you for your patience.

Michael
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Index Match after sorting
Replies
1
Views
648
Eric W
Eric W
E
  • Question Question
Function to return array of 10 largest absolute values while maintaining sign
Replies
3
Views
557
eurobonds
E
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
519
Rnkhch
R
H
  • Question Question
Excel Formula
Replies
0
Views
274
hrayani
H
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
570
Davenil
D

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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