I have got a table set up in one sheet with "transactions". Each row contains a name of a vendor, the amount owed or paid depending on transaction type, and the due date/transaction date. Here is some simplified sample data:
Vendor Date Invoice Payment Vendor A 6/30 $200 Vendor A 6/30 ($200) Vendor B 7/5 $500 Vendor B 7/5 ($500) Vendor C 10/28 $50 Vendor A 10/30 $100 Vendor C 11/15 $50
I have already built a PivotTable from that table to group these transactions by vendor and sum the remainder owed. What I'm trying to figure out is how to, for each vendor, get the next due date (min date of the group, excluding dates < Today()), or if there is no next due date then I want to see the max date for that group.
Here is what my PivotTable looks like, plus the date column I'd like to add (assuming Today() = 10/23):
Vendor Date Owed Vendor B 7/5 - Vendor C 10/28 $100 Vendor A 10/30 $100
I know calling it next due date might not be so accurate if I end up with the date of a payment in that column, but I'm ok with that.
tl;dr : I want to find the next earliest date within each group, or the last date. How do I do this?