• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding Min and Max Dates using multiple filters without using array formula

ajoshi76

Member
Hi

I have 2 worksheets: Sheet 1 and Sheet 2.

Sheet 1 is the Master Db having Application Id, Environment Name, Milestones, Start Date and End Date. Each Application Id can have multiple environment and multiple milestones. Looks like this

Application Id Environment Name Milestones Start Date End Date
App1 Dev M1 01-Jul-2015 31-Aug-2015
App1 Dev M2 15-Aug-2015 15-Sep-2015
App1 Dev M3 01-Sep-2015 13-Sep-2015
App2 Dev M1 01-Jul-2015 31-Aug-2015
App2 Dev M2 15-Aug-2015 15-Sep-2015
App2 Dev M3 01-Sep-2015 13-Sep-2015
App2 UAT M1 01-Jul-2015 31-Aug-2015
App2 UAT M2 15-Aug-2015 15-Sep-2015
App2 UAT M3 01-Sep-2015 13-Sep-2015

Sheet 2 is the Summary level information, table as below
Application Id Environment Name Start Date End Date
App1 Dev
App2 Dev
App2 UAT


Need to know the Start date and End date in Sheet 2 not using array as it has slowed down my excel performance.

Please advise.
 
You could generate the summary using a pivot table and use the Pivot table's Min Max options to get the dates. Should be much faster.

Min Max.png

I've attached the file where I've done this. Let me know if you need any help understanding how to do this.
 

Attachments

  • Min-Max.xlsx
    18.3 KB · Views: 0
Thanks Vivek. Understand that you have used the Pivot and Min within the same for SDate and EDate... But how about a formula to calculate the same?
 
Then I guess an array formula as shown is the only option. I'm not much of a formula expert so hopefully someone else has another solution.

Any particular reason you want it as a formula only?

Min Max 2.png
 

Attachments

  • Min-Max_VD2.xlsx
    19.1 KB · Views: 0
Because my second table is static and needs further updates from stakeholders. Hence, keeping it with pivot and some table with open fields seems tough in pivots. Array has degraded the performance and keeps re-calculating the whole table (3000 rows) everytime sometime updates one date in the master sheet.
Hence, need a way to do it with normal formulas.
 
Back
Top