Forum Discussion
List of names by least recent date in another column
Hi. I have a table of names and dates in Excel, for the last time people filled a certain role. Since it's a job that comes in teams, there are lots of duplicate dates. But I wan to get a list of the six people who least recently did that job.
Doesn't look like I can attach files yet, but the table goes something like this:
| Thomas | 5/16/2023 |
| Genesis | 6/4/2023 |
| Jacob | 1/0/1900 |
| Bryce | 5/23/2023 |
| Jillian | 5/25/2023 |
| JC | 1/0/1900 |
| Stanlee | 5/18/2023 |
| Hay,Jordan | 6/4/2023 |
| McNeese,Pamela Rose | 5/28/2023 |
| Gregston,Johnathon | 5/30/2023 |
This formula just gets me Jacob over and over:
=INDEX($E$5:$E$15,MATCH(MIN($G$5:$G$15),$G$5:$G$15,0))
I think this aggregate formula should do the trick, but I can't figure what sort of thing to put in my countif:
=INDEX($E$5:$e$15,AGGREGATE(15,6,(ROW($G$5:$G$15)-ROW($G$5)+1)/((COUNTIF($G$5:$G$15,"<>"&0))>0),ROWS(B$3:B3)),COLUMNS($B3:B3))
Any help would be appreciated!
=IF(ROW()>7,"",VLOOKUP(COUNTIF($H$2:H2,H2)&H2,CHOOSE({1,2},$E$2:$E$19&$F$2:$F$19,$G$2:$G$19),2,FALSE))An alternative could be this formula along with two helper columns E and H in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=IF(ROW()>7,"",LARGE($F$2:$F$19,ROW(A1)))This formula is in cell H2 and filled down.
=COUNTIF($F$2:F2,F2)This formula is in cell E2 and filled down.
In your first formula =INDEX($E$5:$E$15,MATCH(MIN($G$5:$G$15),$G$5:$G$15,0)), you are using the MIN function to find the minimum date in column G ($G$5:$G$15), and then using MATCH to locate the position of that minimum date in the same range. The corresponding name is then retrieved using INDEX from column E ($E$5:$E$15). However, this formula only returns a single result, which is the name corresponding to the earliest date, and it doesn't handle duplicate dates or provide a list of the six least recent names.
In your second formula =INDEX($E$5:$E$15,AGGREGATE(15,6,(ROW($G$5:$G$15)-ROW($G$5)+1)/((COUNTIF($G$5:$G$15,"<>"&0))>0),ROWS(B$3:B3)),COLUMNS($B3:B3)), you are using the AGGREGATE function to calculate the row numbers of the least recent dates. It uses the (ROW($G$5:$G$15)-ROW($G$5)+1)/((COUNTIF($G$5:$G$15,"<>"&0))>0) part to generate an array of row numbers where the dates are not zero. Then, the AGGREGATE function with option 15 (SMALL) retrieves the smallest row numbers. The corresponding names are then retrieved using INDEX from column E ($E$5:$E$15). This formula has the potential to handle duplicate dates, but the COUNTIF condition "<>"&0 may not be necessary if the date 0 represents an empty or default date value.
This suggested formula =INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12<>0,ROW($A$2:$A$12)-MIN(ROW($A$2:$A$12))+1),ROW(A1))) takes a different approach. It uses an array formula with the SMALL and IF functions to calculate the row numbers of the least recent dates. The IF function checks if the dates in column B ($B$2:$B$12) are not equal to 0, and if true, it generates an array of adjusted row numbers. The SMALL function retrieves the nth smallest row number, where n is determined by the row number of the formula. Finally, the INDEX function retrieves the corresponding names from column A ($A$2:$A$12). By dragging the formula down, it populates the next smallest values and provides a list of the six least recent names.
Overall, the main difference lies in how the formulas determine the least recent dates and retrieve the corresponding names. The suggested formula handles duplicate dates and provides a list of the six least recent names, while your formulas focus on retrieving a single result or may not handle duplicate dates properly.
This seems to result in reproducing the same list as in column A. For reference, I'm using Office 2019 so I can be compatible with the rest of my company.
NikolinoDE
=IF(ROW()>7,"",VLOOKUP(COUNTIF($H$2:H2,H2)&H2,CHOOSE({1,2},$E$2:$E$19&$F$2:$F$19,$G$2:$G$19),2,FALSE))An alternative could be this formula along with two helper columns E and H in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=IF(ROW()>7,"",LARGE($F$2:$F$19,ROW(A1)))This formula is in cell H2 and filled down.
=COUNTIF($F$2:F2,F2)This formula is in cell E2 and filled down.
Hey, this did the trick! Although I did have to swap LARGE for SMALL, since I'm looking for the least recent date. Can you explain how the CHOOSE function affects it?
Here's an explanation for VLOOKUP with CHOOSE.
VLOOKUP with multiple criteria advanced - Excel formula | Exceljet
