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

#### bearcub

##### Well-known Member
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

#### Marcelo Branco

##### MrExcel MVP
Could you provide a small data sample along with expected results?

M.

#### bearcub

##### Well-known Member
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
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
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

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

</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
Thank you Eric, this will definitely give me a unique list.

#### Marcelo Branco

##### MrExcel MVP
Thank you Eric, this will definitely give me a unique list.
I thought you wanted a list of the duplicates, not a unique list...

M.

#### bearcub

##### Well-known Member
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
Thank you Marcelo for the clarification. It works like a charm, thank you for your patience.

Michael

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

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