Forum Discussion
Using sortby and filter with a named range
Howdy. I have the following formula and it works fine.
=FILTER(tblBasketList,(tblBasketList[Crew]="Days")*(tblBasketList[Last]<>"")*(tblBasketList[NoLight]<>"x"))
The main "tblBasketList" is sorted first by Crew, then Unit, then Basket number. I want to do a sort where two different values in the crew column are combined, then sorted in a specific order. I'm able to get filter to show the two together. I'm just having trouble figuring out how to apply Sortby to it.
=FILTER(tblBasketList, (tblBasketList[Last] <>"") * ((tblBasketList[Crew]= "A") + (tblBasketList[Crew] = "Days")) * (tblBasketList[NoLight]<>"x"))
I've tried referencing the tblBasketList columns by name. I've tried doing it by column index. Neither worked, and I'm fairly certain it's just me messing something up. I want to sort it by a column named Unit, and then by BasketNumber. Which are the 5th and 1st columns.
Any advice would be appreciated.
Isn't that a job for SORT?
=SORT(FILTER(tblBasketList, (tblBasketList[Last] <>"") * ((tblBasketList[Crew]= "A") + (tblBasketList[Crew] = "Days")) * (tblBasketList[NoLight]<>"x")), {5, 1})
Alternatively, sort the table before filtering it:
=FILTER(SORTBY(tblBasketList, tblBasketList[Unit], 1, tblBasketList[BasketNumber], 1), (tblBasketList[Last] <>"") * ((tblBasketList[Crew]= "A") + (tblBasketList[Crew] = "Days")) * (tblBasketList[NoLight]<>"x"))
2 Replies
Isn't that a job for SORT?
=SORT(FILTER(tblBasketList, (tblBasketList[Last] <>"") * ((tblBasketList[Crew]= "A") + (tblBasketList[Crew] = "Days")) * (tblBasketList[NoLight]<>"x")), {5, 1})
Alternatively, sort the table before filtering it:
=FILTER(SORTBY(tblBasketList, tblBasketList[Unit], 1, tblBasketList[BasketNumber], 1), (tblBasketList[Last] <>"") * ((tblBasketList[Crew]= "A") + (tblBasketList[Crew] = "Days")) * (tblBasketList[NoLight]<>"x"))
Sort just does one value, which is why I was working on sortby. I didn't think about reversing the filter/sortby order. It works perfectly. Thanks for the help!
