Forum Discussion

puretext's avatar
puretext
Copper Contributor
Jun 05, 2023
Solved

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 t...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Jun 05, 2023

    puretext 

    =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.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 05, 2023

puretext 

=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.

Resources