Showing most recent date for multiple employees

[errollflynn]

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.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

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

 

[errollflynn]

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]

What criteria are you referring to? Here's my sample sheet:

Book1
	A	B															Q
1	Recruiter	Date															Most recent date
2	A	1/1/2021															3/1/2021
3	A	1/2/2021															3/1/2021
4	B	2/2/2021															2/2/2021
5	C	2/3/2021															2/3/2021
6	A	1/5/2021															3/1/2021
7	A	3/1/2021															3/1/2021
8	B	1/4/2021															2/2/2021
9	E	2/2/2021															2/2/2021
10	F
11	E																2/2/2021
12
Sheet9

Cell Formulas
Range	Formula
Q2:Q12	Q2	=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.

 

[errollflynn]

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
	A	B	C	D	E
1	Interview Date	Recruiter		Recruiter	Date Last Used
2	1/4/2021	Khari Shiver			1/0/00
3	1/1/2021	Christine Park			1/0/00
4	1/5/2021	Alicia Quinonez			1/0/00
5	1/5/2021	Fartin Martin			1/0/00
6	1/6/2021	Alicia Quinonez			1/0/00
7	1/3/2021	Christine Park			1/0/00
8	1/10/2021	Fartin Martin			1/0/00
9	1/3/2021	Alicia Quinonez			1/0/00
10	1/14/2021	Alicia Quinonez			1/0/00
11	1/7/2021	Khari Shiver			1/0/00
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=MAXIFS(A2:A11,B2:B11,"Khari Shiver",B2:B11,"Christine Park",B2:B11,"Alicia Quinonez",B2:B11,"Fartin Martin")

Cells with Data Validation
Cell	Allow	Criteria
D2:D11	List	='Validation Data'!$A$2:$A$5
B2:B11	List	='Validation Data'!$A$2:$A$5

 

[Eric W]

Given your layout, I'd suggest something like this:

Book1
	A	B	C	D	E
1	Interview Date	Recruiter		Recruiter	Date Last Used
2	1/4/2021	Khari Shiver		Christine Park	1/3/2021
3	1/1/2021	Christine Park		Fartin Martin	1/10/2021
4	1/5/2021	Alicia Quinonez
5	1/5/2021	Fartin Martin
6	1/6/2021	Alicia Quinonez
7	1/3/2021	Christine Park
8	1/10/2021	Fartin Martin
9	1/3/2021	Alicia Quinonez
10	1/14/2021	Alicia Quinonez
11	1/7/2021	Khari Shiver
12
Sheet9

Cell Formulas
Range	Formula
E2:E11	E2	=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]

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

 

[Eric W]

Happy to help! Thanks for the feedback.

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

 

[errollflynn]

Indeed!