New Contributor

# Adding Sortby to Unique Filter formula creating errors

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

My previous formula was:

=UNIQUE(FILTER(BoM!I13:J2974,BoM!I13:I2974<>""))

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.

=UNIQUE(FILTER(SORTBY(BoM!I13:J2974,BoM!J13:J2974,1),BoM!I13:I2974<>""))

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

# Re: Adding Sortby to Unique Filter formula creating errors

@Chris_Rath Have you tried it this way?

``=SORT(UNIQUE(FILTER(BoM!I13:J2974,BoM!I13:I2974<>"")))``

# Re: Adding Sortby to Unique Filter formula creating errors

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

x

y

z

result will be nothing.

# Re: Adding Sortby to Unique Filter formula creating errors

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.