Forum Discussion
Filter & Choose
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:
- describe in words what the formula does
- 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.
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" )
