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 formula to return a blank cell vs 0?

=FILTER(CHOOSE({1,2,3,4,5,6,7},'Emp.12.27 In'!A2:A14000,'Emp.12.27 In'!D2:D14000,'Emp.12.27 In'!C2:C14000,'Emp.12.27 In'!F2:F14000,'Emp.12.27 In'!E2:E14000,'Emp.12.27 In'!G2:G14000,'Emp.12.27 In'!H2:H14000),'Emp.12.27 In'!I2:I14000='Template-In'!M7,"Ready")

Appreciate any help you can provide. 

 

Using Microsoft 365 Apps for enterprise

 

DH

Formulas and Functions
  • mathetes's avatar
    mathetes
    Silver Contributor
    Jan 04, 2023

    danhill365 

     

    My first response might be that since zero is the mathematical equivalent of blank, why not follow the old maxim "If it ain't broke, don't fix it."

     

    That said, and maybe others can decode and grasp what your formula is doing, I'd need to see it in action. It LOOKS to me as if it may be making something more complicated than it needs to be. I'm wondering if you could do one of the following:

    1. describe in words what the formula does
    2. preferably, make it possible for us to see a simplified version of the spreadsheet, real names removed (it's clearly an employee database of some kind), so we could see it first hand. You can post spreadsheets on OneDrive or GoogleDrive and paste a link here granting edit access.

    By the way, I'm asking as someone with personal professional experience working as the director of the HR/Payroll database of a major corporation. I retired a bit over 20 years ago, but retain the interest in how databases can enhance the management of these most important resources.

     

    I also, for the record, do know what CHOOSE and FILTER do within Excel. I've never seen them put together in tandem as you're doing, not that it couldn't be done. But one of the neat things of Excel is that there are always multiple ways to get from point A to point B, some more efficient than others. That's the other awareness behind my observation and request.

    • danhill365's avatar
      danhill365
      Copper Contributor
      Jul 03, 2023
      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"
  )

         

Share