Forum Discussion

danhill365's avatar
danhill365
Copper Contributor
Jan 04, 2023

Filter & Choose

Hi Community, First time posting so I appreciate any direction. The formula below does pull the intended information however if I leave cell M7 blank the formula returns 0. How can I clean up the ...
Formulas and Functions
danhill365's avatar
danhill365
Copper Contributor
Feels like yesterday I posted this. I'm very appreciative of your response. I did find a solution to my answer. I had to tweak the formula a little.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 03, 2023

danhill365 

CHOOSECOLS can also be used be used to select and reorder columns from an array before filtering rows.  Testing the criterionValue against 'blank'  will avoid returning blank rows.  Not having any blank rows would be a better solution because oversize arrays are inefficient and error-prone.

= FILTER(
      CHOOSECOLS(array, 1,4,3,6,5,7,8), 
      (criteriaColumn=criterionValue) * (criterionValue<>""), 
      "Ready"
  )

 

Resources