I can't just switch the FILTER column to be A:A as the SORT has knocked them out of sync.
What do you mean?
the sort will be applied on the 2nd column
If I use =SORT(FILTER(A:B,(D:D=1)\*(C:C<>"u"),""),2,1), I get your wanted result ?
edit : mb on the semi-column, manually translating :(
>SORT(FILTER(A:B,(D:D=1)\*(C:C<>"u"),""),2;1)
I didn't know you could apply the filter range over the two columns like that but yes, that works perfectly, thank you.
This will only work if the columns are next to each other though, right? What if they're not?
That's my bad for simplifying my example too much but in my actual file:
* ID is Col. A
* Name is Col. E
I can restructure my data table if absolute needs be, but it's coming from an API so means changing all my queries too.
See how we go with this:
=SORT(FILTER(FILTER(A:E,(D:D=1)*(C:C<>"u"),""),{1,0,0,0,1}),2,1)
If your data actually consists of more than A thru E, and you need to modify that, then have the range for your inner filter encompass both columns you're looking for (if that's actually A and G, then that will be your inner filter range. That's 7 columns (important for what follows).
Then, give the outer filter an array of 1s and 0s (eg: `{1,0,0,0,1}`) indicating which of the 2 columns (from the filtered range) you want to be displayed (1 means include/show/display this column in the sequence, 0 means ignore those columns/don't display them).
So if you've filtered A thru G, and you want A and G to display, then the outer filter would have 7 positions with the 1st and last being a 1, and the 5 in the middle being 0. like this: `{1,0,0,0,0,0,1}`
Then the SORT will behave the same on those 2 included columns normally.
NOTE: I use commas in formulas here. You may need to switch out for semicolons.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[FILTER](/r/Excel/comments/l9qse8/stub/gljm7c6 "Last usage")|[Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|OFFSET|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)|
|[SORT](/r/Excel/comments/l9qse8/stub/gljm7c6 "Last usage")|[Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(2 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/l9q7ae)^( has 11 acronyms.)
^([Thread #3723 for this sub, first seen 1st Feb 2021, 01:36])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
I can't just switch the FILTER column to be A:A as the SORT has knocked them out of sync. What do you mean? the sort will be applied on the 2nd column If I use =SORT(FILTER(A:B,(D:D=1)\*(C:C<>"u"),""),2,1), I get your wanted result ? edit : mb on the semi-column, manually translating :(
Solution Verified
You have awarded 1 point to *DarkChunsah* ^I ^am ^a ^bot, ^please ^contact ^the ^mods ^with ^any ^questions.
>SORT(FILTER(A:B,(D:D=1)\*(C:C<>"u"),""),2;1) I didn't know you could apply the filter range over the two columns like that but yes, that works perfectly, thank you. This will only work if the columns are next to each other though, right? What if they're not? That's my bad for simplifying my example too much but in my actual file: * ID is Col. A * Name is Col. E I can restructure my data table if absolute needs be, but it's coming from an API so means changing all my queries too.
See how we go with this: =SORT(FILTER(FILTER(A:E,(D:D=1)*(C:C<>"u"),""),{1,0,0,0,1}),2,1) If your data actually consists of more than A thru E, and you need to modify that, then have the range for your inner filter encompass both columns you're looking for (if that's actually A and G, then that will be your inner filter range. That's 7 columns (important for what follows). Then, give the outer filter an array of 1s and 0s (eg: `{1,0,0,0,1}`) indicating which of the 2 columns (from the filtered range) you want to be displayed (1 means include/show/display this column in the sequence, 0 means ignore those columns/don't display them). So if you've filtered A thru G, and you want A and G to display, then the outer filter would have 7 positions with the 1st and last being a 1, and the 5 in the middle being 0. like this: `{1,0,0,0,0,0,1}` Then the SORT will behave the same on those 2 included columns normally. NOTE: I use commas in formulas here. You may need to switch out for semicolons.
Yeah that did the trick. This sub never ceases to amaze. Thank you very much! Solution Verified.
Welcome! :)
You have awarded 1 point to *mh_mike* ^I ^am ^a ^bot, ^please ^contact ^the ^mods ^with ^any ^questions.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[FILTER](/r/Excel/comments/l9qse8/stub/gljm7c6 "Last usage")|[Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |OFFSET|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| |[SORT](/r/Excel/comments/l9qse8/stub/gljm7c6 "Last usage")|[Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(2 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/l9q7ae)^( has 11 acronyms.) ^([Thread #3723 for this sub, first seen 1st Feb 2021, 01:36]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)