Feb 10 2021 10:44 AM - edited Feb 10 2021 10:44 AM
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?
Feb 10 2021 12:10 PM
Feb 10 2021 12:23 PM
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.
Feb 10 2021 02:07 PM
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.