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:

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

 

With Sortby added:

=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

@Chris_Rath Have you tried it this way?

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

 

@Chris_Rath 

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.

@Riny_van_Eekelen 

 

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.