Good morning,
I've got a fairly simple table from which I am extracting data from in a new worksheet using CHOOSECOLS, VSTACK, & a FILTER. The simple question is how to add a sort to the result set from the formula below. Standard sorting will not work on an array.
The SORTBY would be on columns 8 (Column E, descending) and 6 (Column D, ascending).
Formula is currently =CHOOSECOLS(VSTACK(Table1[#Headers],FILTER(Table1,Table1[New Assign?]=Sheet6!C4,"")),2,5,6,8,9)
Thank you!
DVS
Hi there.
A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.
Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Not sure what you mean byas col 8 is I & col 6 is F, but this will sort on cols 8 & 6The SORTBY would be on columns 8 (Column E, descending) and 6 (Column D, ascending).Formula:
=sort(CHOOSECOLS(VSTACK(Table1[#Headers],FILTER(Table1,Table1[New Assign?]=Sheet6!C4,"")),2,5,6,8,9),{4,3},{-1,1})
Thanks Fluff13! Your solution was almost perfect.
The sort was in the wrong spot as it included the column headings...made the following change and it sorts correctly.
=CHOOSECOLS(VSTACK(Table1[#Headers],SORT(FILTER(Table1,Table1[New Assign?]=Sheet6!C4,""),{4,3},{-1,1})),2,5,6,8,9)
I didn't know how the SORT function and {} brackets work with an array. Excellent info!
Last edited by drvs; 11-28-2023 at 04:42 PM.
Glad to help & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks