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

bearcub

Well-known Member
Joined
May 18, 2005
Messages
608
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
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
608
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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Assuming the IDs ate numeric, not alphanumeric...
See if this example helps - a more efficient formula


A
B
C
1
ID​
Duplicate​
2
3​
3​
3
4​
4​
4
4​
10​
5
3​
11​
6
8​
16​
7
9​
17​
8
10​
19​
9
11​
10
10​
11
11​
12
15​
13
16​
14
16​
15
17​
16
17​
17
18​
18
19​
19
19​
20
19​
21
20​
22

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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
Another option, if it's numeric data:

FGHIJ
1
27List
371
452
573
685
727
838
9822
10233
11144
126666
1333
1422
1544
1666
17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
J3=IF(MAX($F$2:$F$2000)=MAX($J$2:$J2),"",SMALL($F$2:$F$2000,COUNTIF($F$2:$F$2000,"<="&J2)+1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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.
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
608
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
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
608
Thank you Marcelo for the clarification. It works like a charm, thank you for your patience.

Michael
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top