Showing most recent date for multiple employees

errollflynn

New Member
Joined
Jan 25, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello everyone!

I have an issue I've been banging my head against the wall trying to solve. I have a spreadsheet broken down as follows:

Column A: A dropdown list with multiple recruiters names.
Column B: Manually entered date when a recruiter conducted an interview. A recruiter can only do one interview a day.
Column Q, i.e. the column I'm trying to create: Contains the most recent date a recruiter conducted an interview.
Additional columns that do not have any relevance to what I'm trying to accomplish.


I would like to have a column that would show the most recent date a recruiter conducted an interview. For example, recruiter A may have conducted interviews on 1/1/2021, 1/5/2021, and 1/19/2021. I need to create an expression that will first identify the recruiter's name in column A, then evaluate the dates associated with that recruiter's name in column B, and then return the most recent value in column Q.

This is my first time posting on the boards so please forgive me if I did not approach posing the question correctly.

Thank you in advance.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,744
Welcome to the MrExcel forum!

Try:

Excel Formula:
=MAXIFS(B:B,A:A,A2)
 

errollflynn

New Member
Joined
Jan 25, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello Eric and thank you for your reply. This is a start but I've run into an issue with the criteria being in the same column. I'm not sure how to write the expression with the recruiters/criteria in the same column. The examples I have seen have multiple criteria in different columns.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,744
What criteria are you referring to? Here's my sample sheet:

Book1
ABQ
1RecruiterDateMost recent date
2A1/1/20213/1/2021
3A1/2/20213/1/2021
4B2/2/20212/2/2021
5C2/3/20212/3/2021
6A1/5/20213/1/2021
7A3/1/20213/1/2021
8B1/4/20212/2/2021
9E2/2/20212/2/2021
10F 
11E2/2/2021
12 
Sheet9
Cell Formulas
RangeFormula
Q2:Q12Q2=IF(MAXIFS(B:B,A:A,A2)=0,"",MAXIFS(B:B,A:A,A2))


Does this match your sheet? You can use the XL2BB (Excel to Bulletin Board) tool in the response box to show a sample of your sheet, and explain what your data looks like, and what the desired results are. It's easy to set up and use.
 
Last edited:

errollflynn

New Member
Joined
Jan 25, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Below is what I have when I try to use multiple criteria. I see your expression and it works the way I would like mine to. I do not fully understand the arguments as yours is written. For example, yours begins with a standard "=if" and has the "maxifs" nested inside the expression. Also, you have the expression set to 0 and have a "" with no values inside. If you could explain the expression that would be a great help. Thank you for your time on this.

Test Book.xlsx
ABCDE
1Interview DateRecruiterRecruiterDate Last Used
21/4/2021Khari Shiver1/0/00
31/1/2021Christine Park1/0/00
41/5/2021Alicia Quinonez1/0/00
51/5/2021Fartin Martin1/0/00
61/6/2021Alicia Quinonez1/0/00
71/3/2021Christine Park1/0/00
81/10/2021Fartin Martin1/0/00
91/3/2021Alicia Quinonez1/0/00
101/14/2021Alicia Quinonez1/0/00
111/7/2021Khari Shiver1/0/00
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=MAXIFS(A2:A11,B2:B11,"Khari Shiver",B2:B11,"Christine Park",B2:B11,"Alicia Quinonez",B2:B11,"Fartin Martin")
Cells with Data Validation
CellAllowCriteria
D2:D11List='Validation Data'!$A$2:$A$5
B2:B11List='Validation Data'!$A$2:$A$5
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,744
Given your layout, I'd suggest something like this:

Book1
ABCDE
1Interview DateRecruiterRecruiterDate Last Used
21/4/2021Khari ShiverChristine Park1/3/2021
31/1/2021Christine ParkFartin Martin1/10/2021
41/5/2021Alicia Quinonez 
51/5/2021Fartin Martin 
61/6/2021Alicia Quinonez 
71/3/2021Christine Park 
81/10/2021Fartin Martin 
91/3/2021Alicia Quinonez 
101/14/2021Alicia Quinonez 
111/7/2021Khari Shiver 
12
Sheet9
Cell Formulas
RangeFormula
E2:E11E2=IF(D2="","",MAXIFS(A:A,B:B,D2))


As far as the E2 formula goes, it first checks to see if D2 is empty (=""), and if so, just makes the result an empty cell (""). If there is an actual name in D2, it performs the MAXIFS. The A:A is the column with the dates in it. The B:B is the column with the recruiters' names in it, and D2 is the name you're looking for. So it looks down every cell in column B and finds "Christine Park" and keeps track of the corresponding date in column A. Once it finds them all, it takes the largest (most recent date), and displays that.
 

errollflynn

New Member
Joined
Jan 25, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This is fantastic! Thank you so much for all your help.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,744
Happy to help! Thanks for the feedback. :cool:

By the way, it looks like we both like old movies. My avatar is a picture of Harold Lloyd.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,724
Members
415,923
Latest member
Kam80

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top