Creating a shortlist from conditional formatting or date range across multiple columns

[Davidm54]

So I have a table with a list of key dates for clients. First columns are client name, then there's 8+ columns with various dates that are important. I have used conditional formatting to highlight cells that are 14 up to 14 days past today's date and 14 days prior. 

Because there are so many dates, I'd like to condense the info even more.

Out of those 8 columns (and 20 names/rows) there's generally only 5-10 cells that are actually highlighted. I'd like a way to present the data as per below pics. Any suggestions welcome!

(mock up spreadsheet attached.)

 

[#2]

This needs VBA macro,, if you are comfortable with it then edit your post & add the request !!

[#3 · Accepted solution]

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

[#4]

As soon as you say it, it's obvious. lol. Unpivot will be perfect. Thanks.

[#5]

@Davidm54 

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

[#6]

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.