Adding Sortby to Unique Filter formula creating errors

New Contributor


I wanted to add a Sortby function to an existing Unique/Filter formula that was working previously.


My previous formula was:


It was pulling columns I and J from another sheet but only for rows where column I was unique and non-blank.  Again this is working.  With the data I was using it created an array with 18 rows and 2 columns - all of the unique items in column I with the corresponding item in column J.


With Sortby added:


Intending to sort the same data by column J.

The result mostly worked, but left out two of the unique items from column I that previously were there when the formula had no Sortby.  The array created is 17 rows and 2 columns.  One of the cells that was supposed to be a unique item from column I is now a 0 (zero).  There are no 0's in my original data.  The other just isn't there (thus the 17 rows instead of 18).  The data is sorted by column J as intended and all items that are there are matched correctly as they are in columns I and J.


Any thoughts to why this is happening?


3 Replies

@Chris_Rath Have you tried it this way?




Change the order, first filter when sort. If you have

a  1 x

b  2 y

c  3 z

and you filter it on x in last column, you have first row.


In you case

c  3 z

b  2 y

a  1 x

you filter above sorted range on same




result will be nothing.



That was my first try but that sorts by column I and I wanted to sort by column J while filtering for uniques in column I.