Search code examples
google-sheets

Unable to filter and sort multiple columns with this filter/sort/map function?

I have a function that joins in columns from multiple sheets, concatenates comments and brings in maximum value from "statuses" sheet.

I want the ability to click on my headers and sort column A, and then go and sort another column and more if I need to. I currently only have the ability to sort by one column with this:

=sort( 
  filter( 
    hstack( 
      choosecols('Raw Data'!A3:Z,5,21,4,3,9), 
      iferror(map('Raw Data'!D3:D, lambda(Σ, if(Σ="",,textjoin(char(10),1,sort(filter({TEXT(Comments!C:C,"m/d/yy : ")&Comments!B:B},Comments!A:A=Σ),filter(Comments!C:C,Comments!A:A=Σ),))))),""), 
      xlookup('Raw Data'!D3:D,Statuses!A:A,Statuses!B:B,,,-1), choosecols('Raw Data'!A3:Z,6,23,7,11,1,2) 
    ), 
    'Raw Data'!A3:A <> "" 
  ), 
  column(E1), false  
)

Is there a way to add in all columns in column(E1), false sort of like columns(1:13),false ?

Link to sheet is here and the formula is in cell A2.

Again, I want the ability to sort and filter any number of columns in sequence that I want just like I would with a normal worksheet. Is this even possible?

Solution

  • I haven't found a way to add that automatically, but I propose you this workaround that sorts the range by each column with the help of REDUCE and a decreasing sequence of numbers of columns:

    =REDUCE( 
  filter( 
    hstack( 
      choosecols('Raw Data'!A3:Z,5,21,4,3,9), 
      Iferror(map('Raw Data'!D3:D, lambda(Σ, if(Σ="",,textjoin(CARACTER(10),1,sort(filter({TEXT(Comments!C:C,"m/d/yy : ")&Comments!B:B},Comments!A:A=Σ),filter(Comments!C:C,Comments!A:A=Σ),))))),""), 
      XLOOKUP('Raw Data'!D3:D,Statuses!A:A,Statuses!B:B,,,-1), choosecols('Raw Data'!A3:Z,6,23,7,11,1,2) 
    ), 
    'Raw Data'!A3:A <> "" 
  ),SEQUENCE(13,1,13,-1),
LAMBDA(a,v,SORT(a,v,FALSE)))

    enter image description here

    UPDATE TO ADD HEADERS

    =VSTACK(
HSTACK(choosecols('Raw Data'!A1:Z1,5,21,4,3,9),"Comments","Status",choosecols('Raw Data'!A1:Z1,6,23,7,11,1,2)),
REDUCE( 
  filter( 
    hstack( 
      choosecols('Raw Data'!A3:Z,5,21,4,3,9), 
      IFERROR(map('Raw Data'!D3:D, lambda(Σ, IF(Σ="",,textjoin(CHAR(10),1,sort(filter({TEXT(Comments!C:C,"m/d/yy : ")&Comments!B:B},Comments!A:A=Σ),filter(Comments!C:C,Comments!A:A=Σ),))))),""), 
      XLOOKUP('Raw Data'!D3:D,Statuses!A:A,Statuses!B:B,,,-1), choosecols('Raw Data'!A3:Z,6,23,7,11,1,2) 
    ), 
    'Raw Data'!A3:A <> "" 
  ),SEQUENCE(13,1,13,-1),
LAMBDA(a,v,SORT(a,v,FALSE))))

    enter image description here