Highlight rows based on multiple cell values by date

[nitaly]

For each row where the labor category and employee name is the same I need the earliest invoice date. For example I need to know the earliest date Jack Doe invoiced as Executive Accountant I by having that row highlighted. The the earliest date Jack Doe invoiced as a Acct Executive by highlighting that row. I have a thousands of line items to deal with so figuring this out will save a lot of time.

Invoice Date	Labor Category	Employee Name
11/20/2012	Executive Accountant I	Doe, Jack
12/20/2012	Executive Accountant I	Doe, Jack
2/26/2014	Acct. Executive	Doe, Jack
2/26/2014	Acct. Executive	Doe, Jack
4/22/2014	Acct. Executive	Doe, Jack
8/22/2013	Executive Accountant II	Doe, Jack
9/24/2013	Executive Accountant II	Doe, Jack
10/24/2013	Executive Accountant II	Doe, Jack
4/30/2014	Account Executive II	Doe, Jack
6/24/2014	Account Executive II	Doe, Jack
10/27/2014	Account Executive II	Doe, Jack
10/6/2014	Senior Executive	Doe, Jack
10/8/2014	Senior Executive	Doe, Jack
12/1/2014	Executive Accountant II	Doe, Jack
3/28/2016	Senior Executive	Doe, Jack
4/21/2016	Senior Executive	Doe, Jack
5/31/2016	Senior Executive	Doe, Jack
2/24/2015	Sr. Acct. Supervisor	Smith, Jane
2/10/2015	Account Supervisor	Smith, Jane
2/24/2015	Account Supervisor	Smith, Jane
3/25/2015	Account Supervisor	Smith, Jane

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[JoeMo]

Rather than highlighting, why not just extract the earliest date for any combination of Labor Category and Employee Name? Copy G2 down and adjust the ranges to match your database>

Excel Workbook
	A	B	C	D	E	F	G
1	Invoice Date	Labor Category	Employee Name	*	Labor Category	Employee Name	Earliest Date
2	11/20/2012	Executive Accountant I	Doe, Jack	*	Executive Accountant I	Doe, Jack	11/20/2012
3	12/20/2012	Executive Accountant I	Doe, Jack	*	Acct. Executive	Doe, Jack	2/26/2014
4	2/26/2014	Acct. Executive	Doe, Jack	*	Senior Executive	Doe, Jack	10/6/2014
5	2/26/2014	Acct. Executive	Doe, Jack	*	Account Supervisor	Smith, Jane	2/10/2015
6	4/22/2014	Acct. Executive	Doe, Jack	*	*	*	*
7	8/22/2013	Executive Accountant II	Doe, Jack	*	*	*	*
8	9/24/2013	Executive Accountant II	Doe, Jack	*	*	*	*
9	10/24/2013	Executive Accountant II	Doe, Jack	*	*	*	*
10	4/30/2014	Account Executive II	Doe, Jack	*	*	*	*
11	6/24/2014	Account Executive II	Doe, Jack	*	*	*	*
12	10/27/2014	Account Executive II	Doe, Jack	*	*	*	*
13	10/6/2014	Senior Executive	Doe, Jack	*	*	*	*
14	10/8/2014	Senior Executive	Doe, Jack	*	*	*	*
15	12/1/2014	Executive Accountant II	Doe, Jack	*	*	*	*
16	3/28/2016	Senior Executive	Doe, Jack	*	*	*	*
17	4/21/2016	Senior Executive	Doe, Jack	*	*	*	*
18	5/31/2016	Senior Executive	Doe, Jack	*	*	*	*
19	2/24/2015	Sr. Acct. Supervisor	Smith, Jane	*	*	*	*
20	2/10/2015	Account Supervisor	Smith, Jane	*	*	*	*
21	2/24/2015	Account Supervisor	Smith, Jane	*	*	*	*
22	3/25/2015	Account Supervisor	Smith, Jane	*	*	*	*
Sheet2

 

[nitaly]

Thanks for the help Joe. I entered and executed the array but I'm only getting a 0 for an answer on all cells. Any suggestions?

 

[JoeMo]

Thanks for the help Joe. I entered and executed the array but I'm only getting a 0 for an answer on all cells. Any suggestions?

Enter the formula in G2, then confirm it by holding down the shift and ctrl keys and pressing Enter. Then simply copy G2 down. Be sure all the cols A, B and C ranges are absolute.

 

[nitaly]

I held down the Shift+Ctrl then Enter and copied the formula down. I know it worked because the {} appeared afterward. Here is the formula I used.. =MIN(IF(($B$2:$B$6848=E2)*($C$2:$C$6848=F2),$A$2:$A$6848))

 

[JoeMo]

I held down the Shift+Ctrl then Enter and copied the formula down. I know it worked because the {} appeared afterward. Here is the formula I used.. =MIN(IF(($B$2:$B$6848=E2)*($C$2:$C$6848=F2),$A$2:$A$6848))

Can you confirm that col A holds the invoice dates, col B the Labor Category, col C the Employee Name? Also confirm that col E holds the specific Labor Category of interest, and col F the specific employee name of interest.

EDIT: If there is no match in your database to the specific labor category and employee name (cols E & F) the formula will return 0 (1/1/1900 if cell is formatted as date). The matches MUST be EXACT and the Invoice dates must be Excel serial dates NOT TEXT dates.

 

[nitaly]

I think I figured out the issue but wanted to run it by you. I copied column's B and C to E and F and then the array formula in G. The only issue I see is I get a value for every line as you can see below. I can just remove duplicates to fix that if everything else is correct. Is there any issues with what I described?

A	B	C		E	F	G
Invoice Date	Labor Category	Employee Name		Labor Category	Employee Name	Invoice Date
11/20/2012	Executive Accountant I	Doe, Jack		Executive Accountant I	Doe, Jack	11/20/2012
12/20/2012	Executive Accountant I	Doe, Jack		Executive Accountant I	Doe, Jack	11/20/2012
2/26/2014	Acct. Executive	Doe, Jack		Acct. Executive	Doe, Jack	2/26/2014
2/26/2014	Acct. Executive	Doe, Jack		Acct. Executive	Doe, Jack	2/26/2014
4/22/2014	Acct. Executive	Doe, Jack		Acct. Executive	Doe, Jack	2/26/2014
8/22/2013	Executive Accountant II	Doe, Jack		Executive Accountant II	Doe, Jack	8/22/2013
9/24/2013	Executive Accountant II	Doe, Jack		Executive Accountant II	Doe, Jack	8/22/2013
10/24/2013	Executive Accountant II	Doe, Jack		Executive Accountant II	Doe, Jack	8/22/2013
4/30/2014	Account Executive II	Doe, Jack		Account Executive II	Doe, Jack	4/30/2014
6/24/2014	Account Executive II	Doe, Jack		Account Executive II	Doe, Jack	4/30/2014
10/27/2014	Account Executive II	Doe, Jack		Account Executive II	Doe, Jack	4/30/2014
10/6/2014	Senior Executive	Doe, Jack		Senior Executive	Doe, Jack	10/6/2014
10/8/2014	Senior Executive	Doe, Jack		Senior Executive	Doe, Jack	10/6/2014
12/1/2014	Executive Accountant II	Doe, Jack		Executive Accountant II	Doe, Jack	8/22/2013
3/28/2016	Senior Executive	Doe, Jack		Senior Executive	Doe, Jack	10/6/2014
4/21/2016	Senior Executive	Doe, Jack		Senior Executive	Doe, Jack	10/6/2014
5/31/2016	Senior Executive	Doe, Jack		Senior Executive	Doe, Jack	10/6/2014
2/24/2015	Sr. Acct. Supervisor	Smith, Jane		Sr. Acct. Supervisor	Smith, Jane	2/24/2015
2/10/2015	Account Supervisor	Smith, Jane		Account Supervisor	Smith, Jane	2/10/2015
2/24/2015	Account Supervisor	Smith, Jane		Account Supervisor	Smith, Jane	2/10/2015
3/25/2015	Account Supervisor	Smith, Jane		Account Supervisor	Smith, Jane	2/10/2015

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[JoeMo]

Remove duplicates from E & F, then put the formula in G and copy it down.

 

[nitaly]

Just say I have corresponding data, like Contract Number & Hourly Rate, that I want to bring over with the associated earliest invoice date.

A	B	C	D	E
Invoice Date	Labor Category	Employee Name	Contract Number	Hourly Rate
11/20/2012	Executive Accountant I	Doe, Jack	11	25.00
12/20/2012	Executive Accountant I	Doe, Jack	11	23.00
2/26/2014	Acct. Executive	Doe, Jack	22	30.00
2/26/2014	Acct. Executive	Doe, Jack	22	29.00
4/22/2014	Acct. Executive	Doe, Jack	22	30.00
8/22/2013	Executive Accountant II	Doe, Jack	56	81.00
9/24/2013	Executive Accountant II	Doe, Jack	56	85.00
10/24/2013	Executive Accountant II	Doe, Jack	56	85.00
4/30/2014	Account Executive II	Doe, Jack	84	41.00
6/24/2014	Account Executive II	Doe, Jack	66	44.00
10/27/2014	Account Executive II	Doe, Jack	87	44.00
10/6/2014	Senior Executive	Doe, Jack	96	22.00
10/8/2014	Senior Executive	Doe, Jack	22	23.00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>