My latest favorite prompt solution just gave me the ultimate solution when embedded Copilot couldn't!
I asked ChatGPT 4.1 to help me figure out how to generate a new column that will give me the maximum plan based on max effdt (data from PS) and then compare that plan to a hard coded year value from an external file. When I asked Excel Copilot for assistance the result was not even close to what I expected to get back and it had errors.
This was my prompt to ChatGPT 4.1:
I have some data in this excel file and i need to indicate in a separate column what plan the entity (ID) in column A was assigned in or before the year indicated in the last column (Participation Year). There can be multiple changes in the Plan column that are tracked by the Effective Date column. But I need to make sure to pick/display only one per plan type. That one plan per ID and Plan Type needs to be equal to or less than the Participation Year. How Can I do this? To reiterate, for each row, I want to indicate the latest plan (by Effective Date) for each ID + Plan Type, where the Effective Date is less than or equal to the Participation Year. And, crucially, for each ID and Plan Type,I only want to display one matching plan (presumably the latest/best match).
The AI provided a solid three-step solution that I was able to apply with a little bit of editing (had to account for a YEAR function to convert the effdt to a proper format): a) convert the date to a good formet, b) add helper column with the latest effective date per ID & Plan Type (≤ Participation Year), and c) add the final column: Plan corresponding to the latest effective date per ID & Plan Type (this one included an array formula that ChatGPT correctly noted needs to be activated with Ctrl+Shift+Enter; in Excel 365/2021 just Enter.).
I attached the sample solution for anyone who is interested.
************
Used MAXIFS to find the latest effective date ≤ participation year per ID & Plan Type.
=MAXIFS(D:D, A:A, A2, C:C, C2, D:D, "<=" & DATE(E2,12,31))
I had to adjust with the following:
=YEAR(MAXIFS(D:D, A:A, A2, C:C, C2, D:D, "<=" & DATE(E2,12,31)))
Used INDEX/MATCH to return the Plan that matches that latest effective date:
=IFERROR(
INDEX(B:B, MATCH(1, (A:A=A2) * (C:C=C2) * (D:D=F2), 0)),
"")
I had to adjust with the following:
IFERROR(INDEX(B:B,MATCH(1,(A:A=A2)*(C:C=C2)*(YEAR(D:D)=F2),0)),"")
------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee
Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------
Original Message:
Sent: 07-09-2025 12:09 PM
From: Alexandra Green
Subject: What's Your Go-To AI Prompt?
What's your favorite AI prompt right now-the one that makes your life easier, your work sharper, or your emails faster?
I'll go first! One of my go-to moves is typing a messy brain dump into ChatGPT and then asking it to:
"Organize and polish this into [an email, Teams message, discussion post, etc.]."
It's been a total time-saver and helps me communicate more clearly (and faster!).
Now your turn-what prompt is in your regular rotation?
#AI #chatgpt
------------------------------
Alexandra Green
Senior Community Manager
Higher Education User Group
------------------------------