I have a spreadsheet that is updated weekly with a backlog of buyer names and the homes they purchased as well as details of the purchase such as the date we project the house will close ("projected close date"). (The house needs to be built before it can close, so the projected close date fluctuates depending on construction timing.) Each buyer has a unique buyer number but there are repeat rows of the same buyer because the the transaction will appear weekly in the backlog until the home is closed. See attachment for an example of how one buyer's projected close date changes over time.

I want to group by buyer number and then have a column with the most recently projected close date from each transaction. I'm not looking for the maximum projected close date but rather the projected close date from our most recent projection (so I'm looking for the projected close date from a row with the most recent report date that pertains to that buyer number. See highlight on attachment for which value I want to pull)



I was able to group by buyer number by setting up a query and grouping by that column, but I haven't figured out how to then insert a column with the corresponding projected close dates from the most recent report date.

This is my first time posting in a forum so please feel free to let me know if I need to provide additional details etc. I look forward to a response!

-Rachel

Attached Images

BacklogScreenshot.PNG (14.2 KB, 11 views)