Forum Discussion
Help with filtering out data
PeterBartholomew1 's formula only works in Excel for Microsoft 365 Insiders.
In the attached workbook you'll find an 'old-fashioned' formula that should work in all versions of Excel. It uses a helper column.
First and foremost you need to be using Excel 365.
FILTER makes it easy to return multiple values that meet a criterion and the BYROW checks where the selected person appears within the table
= FILTER(weekCommencing, BYROW(holiday = person, OR))
PeterBartholomew1 's formula only works in Excel for Microsoft 365 Insiders.
In the attached workbook you'll find an 'old-fashioned' formula that should work in all versions of Excel. It uses a helper column.
Using a formula that requires 365 insider was an error on my behalf. I should have used the Lambda function rather than an eta-reduced version
= FILTER(weekCommencing, BYROW(holiday = person, LAMBDA(x, OR(x))))Well done on your formula, BTW. If you had kicked me, I would have sent you a copy of my workbook rather than having you generate something from scratch. I had got a reasonable start by using Windows/Shift/S to bring the data in as a picture.
By the time I had worked your solution through to get
= IFERROR(INDEX(weekCommencing, SMALL(IF(helper, SEQUENCE(12), ""), @k)), "")I finally understood it! By now, I have completely lost the knack of reading 'old-style' formulas 😓.
- Thanks you very much