Excel : Find Ultimate and Penultimate Values for Unique Id

Date and time is represented by number where the integer part represent date and part after decimal represent time. More about date in Excel.

So basically you are looking for the largest and second largest number. Use formula LARGE.

Ultimate=LARGE(Table1[Date]*(Table1[UID]=[@UID]),1)
Penultimate=LARGE(Table1[Date]*(Table1[UID]=[@UID]),2)

If you are using one of these: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel for iPad Excel Web App Excel for iPhone Excel for Android tablets Excel for Android phones. or newer.

Just use these formulas.

In older Excel version: You have to input it as array formula.

It must must be confirmed with CTRL+SHIFT+ENTER. if you done it correctly the formula will be wrapped in {}. This means that formula is computed as array formula.

How it works:

1. (Table1[UID]=[@UID]) Returns matrix of TRUE or FALSE for with same size as Table1[UID] column.

2. Table1[Date]*(Table1[UID]=[@UID]) date column is multiplied by result formula from 1. Excel works with boolean values this way TRUE = 1, FALSE = 0. Result of this step is matrix with number values where all rows where UID is different from [@UID] are 0

3. Finally the LARGE formula finds n-th largest value from result of previous step. You get number represent date (format it as date)

There are a few caveats:

• When there is no Ultimate/Penultimate time for UID you get 0. Use IF function to treat this exceptions.
• If there are two same most recent dates for one UID in Table1, the Penultimate time will be same as Ultimate (1st largest and 2nd largest value are same)