Get Last Date for Code Group with Duplicates
How to display the Last Date for the specific code that is repeated
In this Excel example tutorial I will show how we can for a specific condition/criteria (in the this case it is a code or a specific text) to display the Last Date, if the code or text that is repeated if there are duplicates.
Look at the situation in the picture below. Note the numbers in column 'A' denoting codes, values, or text. Each code belongs to a specific date in column 'B'. Our goal is for the specific code (number, value or text) as a result of the show last date of the year.
This task or problem can be solved using Excel AGGREGATE function . More details of how the Aggregate function, see the respective link. I'm here to try to explain the formula to return the last date for the particular condition.
Column 'D' in the picture above you see the conditions that we are looking for the latest and greatest date in the year.
The formula in cell 'E2' is the next. Copy a formula down.
You notice the first argument syntax formula and this is number 14. This number indicates that in the formula we use the LARGE function.
The second argument is the number 6 and that means that we want to ignore errors in a range of data and eventual error #DIV/0! that will appear during the calculation. See Evaluate the formulas for themselves.
The third argument reference, contains the specific expression of a range of cells that separates the B/A and in fact this is related to the condition in the cell 'D2'. The range of cells for the two columns should be identical.
The fourth argument is the number 1, and this argument indicates that the formula should return the first date the largest with respect to use LARGE function. When you would return the second biggest value then this number need to be 2. I hope you understand how the AGGREGATE function work.
Also you can see tutorial Last data from Column or Row.