Filter & Choose

[danhill365]

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

[#2]

@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.

[#3]

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.

[#4]

@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"
  )