how to list unique numbers sorted descending

raghuram.star

Board Regular
can you please help me get the list of unique numbers from a list sorted in descending order

I have below array formula, this is taking too long to refresh the list, as i have almost 15000 rows source data and the below formula is used 53 to get to 53 from the list

=IFERROR(INDEX(\$A\$1:\$A\$15000, MATCH(MIN(IF(COUNTIF(\$B\$1:B2, \$A\$1:\$A\$15000)=0, 1, MAX((COUNTIF(\$A\$1:\$A\$15000, ">"&\$A\$1:\$A\$15000)+1)*2))*(COUNTIF(\$A\$1:\$A\$15000, ">"&\$A\$1:\$A\$15000)+1)), COUNTIF(\$A\$1:\$A\$15000, ">"&\$A\$1:\$A\$15000)+1, 0)),"")

I have 15000 rows of data in Column A - from A1 to A15000
Formula in Column B - from Cell B2

Any help in getting the same result faster will be a great help.

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Michael M

Well-known Member
could you not just use Data>>Advanced filter>>unique records only

XOR LX

Well-known Member
Hi.

If you insist upon a formula solution, then, since you say that the values in column A are numbers, I don't see why you are using a COUNTIF construction.

Simply:

=LARGE(IF(FREQUENCY(A\$1:A\$15000,A\$1:A\$15000),A\$1:A\$15000),ROWS(\$1:1))

and copied down will suffice, though of course this assumes that every single cell within the range A1:A15000 contains a number (you don't mention blanks, so I assume that there are none within your range).

Regards

raghuram.star

Board Regular
=LARGE(IF(FREQUENCY(A\$1:A\$15000,A\$1:A\$15000),A\$1:A\$15000),ROWS(\$1:1))

this did the trick, this is way faster and it saved atleast 10 seconds for each refresh
thank you so much for the help

You're welcome!

Replies
3
Views
509
Replies
4
Views
275
Replies
7
Views
390
Replies
3
Views
321
Replies
4
Views
284

1,196,007
Messages
6,012,831
Members
441,732
Latest member
Ayon

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.

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

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