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

[bearcub]

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?

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]

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

M.

 

[bearcub]

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.

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]

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]

Another option, if it's numeric data:

	F	G	H	I	J
1
2	7				List
3	7				1
4	5				2
5	7				3
6	8				5
7	2				7
8	3				8
9	8				22
10	2				33
11	1				44
12	66				66
13	33
14	22
15	44
16	66
17

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

Sheet1​

Worksheet Formulas Cell Formula 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]

Thank you Eric, this will definitely give me a unique list.

 

[Marcelo Branco]

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]

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]

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

Michael