Adding Sortby to Unique Filter formula creating errors

%3CLINGO-SUB%20id%3D%22lingo-sub-2120765%22%20slang%3D%22en-US%22%3EAdding%20Sortby%20to%20Unique%20Filter%20formula%20creating%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120765%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wanted%20to%20add%20a%20Sortby%20function%20to%20an%20existing%20Unique%2FFilter%20formula%20that%20was%20working%20previously.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20previous%20formula%20was%3A%3C%2FP%3E%3CP%3E%3DUNIQUE(FILTER(BoM!I13%3AJ2974%2CBoM!I13%3AI2974%26lt%3B%26gt%3B%22%22))%3C%2FP%3E%3CP%3EIt%20was%20pulling%20columns%20I%20and%20J%20from%20another%20sheet%20but%20only%20for%20rows%20where%20column%20I%20was%20unique%20and%20non-blank.%26nbsp%3B%20Again%20this%20is%20working.%26nbsp%3B%20With%20the%20data%20I%20was%20using%20it%20created%20an%20array%20with%2018%20rows%20and%202%20columns%20-%20all%20of%20the%20unique%20items%20in%20column%20I%20with%20the%20corresponding%20item%20in%20column%20J.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20Sortby%20added%3A%3C%2FP%3E%3CP%3E%3DUNIQUE(FILTER(SORTBY(BoM!I13%3AJ2974%2CBoM!J13%3AJ2974%2C1)%2CBoM!I13%3AI2974%26lt%3B%26gt%3B%22%22))%3C%2FP%3E%3CP%3EIntending%20to%20sort%20the%20same%20data%20by%20column%20J.%3C%2FP%3E%3CP%3EThe%20result%20mostly%20worked%2C%20but%20left%20out%20two%20of%20the%20unique%20items%20from%20column%20I%20that%20previously%20were%20there%20when%20the%20formula%20had%20no%20Sortby.%26nbsp%3B%20The%20array%20created%20is%2017%20rows%20and%202%20columns.%26nbsp%3B%20One%20of%20the%20cells%20that%20was%20supposed%20to%20be%20a%20unique%20item%20from%20column%20I%20is%20now%20a%200%20(zero).%26nbsp%3B%20There%20are%20no%200's%20in%20my%20original%20data.%26nbsp%3B%20The%20other%20just%20isn't%20there%20(thus%20the%2017%20rows%20instead%20of%2018).%26nbsp%3B%20The%20data%20is%20sorted%20by%20column%20J%20as%20intended%20and%20all%20items%20that%20are%20there%20are%20matched%20correctly%20as%20they%20are%20in%20columns%20I%20and%20J.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%20to%20why%20this%20is%20happening%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121031%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20Sortby%20to%20Unique%20Filter%20formula%20creating%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20was%20my%20first%20try%20but%20that%20sorts%20by%20column%20I%20and%20I%20wanted%20to%20sort%20by%20column%20J%20while%20filtering%20for%20uniques%20in%20column%20I.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120937%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20Sortby%20to%20Unique%20Filter%20formula%20creating%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F962597%22%20target%3D%22_blank%22%3E%40Chris_Rath%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20the%20order%2C%20first%20filter%20when%20sort.%20If%20you%20have%3C%2FP%3E%0A%3CP%3Ea%26nbsp%3B%201%20x%3C%2FP%3E%0A%3CP%3Eb%26nbsp%3B%202%20y%3C%2FP%3E%0A%3CP%3Ec%26nbsp%3B%203%20z%3C%2FP%3E%0A%3CP%3Eand%20you%20filter%20it%20on%20x%20in%20last%20column%2C%20you%20have%20first%20row.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20you%20case%3C%2FP%3E%0A%3CP%3Ec%26nbsp%3B%203%20z%3C%2FP%3E%0A%3CP%3Eb%26nbsp%3B%202%20y%3C%2FP%3E%0A%3CP%3Ea%26nbsp%3B%201%20x%3C%2FP%3E%0A%3CP%3Eyou%20filter%20above%20sorted%20range%20on%20same%3C%2FP%3E%0A%3CP%3Ex%3C%2FP%3E%0A%3CP%3Ey%3C%2FP%3E%0A%3CP%3Ez%3C%2FP%3E%0A%3CP%3Eresult%20will%20be%20nothing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120922%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20Sortby%20to%20Unique%20Filter%20formula%20creating%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F962597%22%20target%3D%22_blank%22%3E%40Chris_Rath%3C%2FA%3E%26nbsp%3BHave%20you%20tried%20it%20this%20way%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORT(UNIQUE(FILTER(BoM!I13%3AJ2974%2CBoM!I13%3AI2974%26lt%3B%26gt%3B%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.