Min and Max formula

[kdestef1]

Hi all, hoping someone out there can help with a formula regarding Min and Max date. The snapshot image I've attached is for a patient who had surgery and was administered medication during the surgery. The data is showing what appears to be duplicate rows, but the query is actually creating a new row when the administration date/times change for the documentation of the administered medication over the course of several days. This is also demonstrated in the example Excel file I've attached.

What I would like to achieve is a formula that searches the unique ID's called "HAR" in column A. If the ID [HAR] matches the ID [HAR] above it, then I would like the formula to take the Min date/time from column Y, "First_MedAdminDate" and the Max date/time from column Z, "Last_MedAdminDate" and place these results on one row in separate columns. I'm also thinking there needs to be a nested formula that also looks at column AC, "Med Order Route" and if it changes, then it repeats the Min and Max formulas on that row. Essentially I'm hoping for it to look like the snapshot I've attached allowing me to eliminate columns Y & Z altogether so I can remove duplicates from the dataset.

Hopefully this makes sense with the attachment examples I've provided.

Thanks!
kdestef1

Min Max Example.JPG

[dflak]

You should be able to do this with a pivot table.

I converted the data into an Excel Table mainly because Excel tables "know" how big they are and formulas, charts and pivot tables built on them automatically adjust to the amount of data in the table.

Then I built the pivot table shown on Sheet2.