Hi Voltrader,
You said you already had the list of names with duplicates so I didn't try and create that but be aware if you miss one then it won't report the 2nd+ date entry. Also it must have that "None" in cells without a date because if you leave it null that would be treated as a Jan 1st 1900 date.
Yes, you need to change the range used if you have more data as my formula assume you start in row 1 and go to row 9. You can change that range to row 1 to row 9,999 without having much impact on speed of execution.
To return multiple values from a list you will usually use SMALL to get ascending and LARGE to get descending but these are array formulas so for quicker execution you can use AGGREGATE where the 15 says select its SMALL function and 6 says to ignore errors within the AGGREGATE as a mismatch on the selection gives a #DIV/0 error.
The $B$1:$J$9 part tells AGGREGATE where it will get the values from and the / ($A$1:$A$9=M1) tells it to divide each value by the logical check of the name matching the one currently looked at in your list, so if I'm looking in the Bob row for a Bob then it divides by the logical result Bob=Bob which is 1 so the date divided by the date = the date and it's returned. If I've reached the Jim row and I'm looking for Bob then the logical result is FALSE or zero so I get the date divided by 0 which generates a #DIV/0 error but AGGREGATE ignores that as we used the option 6 to suppress errors.
The last part of AGGREGATE is for which SMALL part you want, 1=smallest, 2=next smallest, etc. COUNTIF($M$1:$M1,M1) will return a 1 against the first list of Bob but when we get to the next Bob entry COUNTIF($M$1:$M2,M2) returns 2 so it looks for the next highest date.
I could also prevent the #NUM in the AGGREGATE function by telling it when to stop. If I do a COUNTIF of all cells greater than zero, so a date, and put that in a cell, in my example P2, then I can use an IF to display nulls after that many have been retrieved.
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P |
|---|
| 1 | Name | Date 1 | Date 2 | Date 3 | Date 4 | Date 5 | Date 6 | Date 7 | Date 8 | Date 9 | | | Bob | 1/1/2020 | | Count of Dates |
|---|
| 2 | Bob | None | 1/1/2020 | None | None | None | 1/3/2020 | None | None | None | | | Bob | 1/3/2020 | | 5 |
|---|
| 3 | Stanley | 1/10/2020 | None | None | None | None | None | None | None | None | | | Stanley | 1/10/2020 | | |
|---|
| 4 | Jim | None | None | None | 11/7/2019 | 12/15/2019 | None | None | None | None | | | Jim | 11/7/2019 | | |
|---|
| 5 | | | | | | | | | | | | | Jim | 12/15/2019 | | |
|---|
| 6 | | | | | | | | | | | | | | | | |
|---|
|
|---|
