Forum Discussion
Creating a shortlist from conditional formatting or date range across multiple columns
Davidm54 I would use Power Query for this. No VBA needed. First unpivot the raw data and add a conditional column that checks every date against the rule of today plus or minus 14 days, and filter only those that qualify. Then load back to Excel and apply the CF rules if you want to add the colours. Expand your raw data table and refresh the query.
See attached.
Just how easy, or difficult, this is to do with a formula very much depends on the version of Excel that you are using.
The Lambda function would 'package the basic unpivot and filter steps better.
There is alternative of filtering first by using LARGE over numeric 'coordinates' for the cells you require and then using the coordinates to lookup row and column reference data.
Note: The 'coordinates' could be formed from =1000000*column+row
- Thanks for that Peter. Its always good to have the formula options and see new methods. And for the moments where power query doesn't cover things.
