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.

[TABLE="width: 487"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Invoice Date[/TD]
[TD]Labor Category[/TD]
[TD]Employee Name[/TD]
[/TR]
[TR]
[TD]11/20/2012[/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]12/20/2012[/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]2/26/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]2/26/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]4/22/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]8/22/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]9/24/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]10/24/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]4/30/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]6/24/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]10/27/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]10/6/2014[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]10/8/2014[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]12/1/2014[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]3/28/2016[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]4/21/2016[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]5/31/2016[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[/TR]
[TR]
[TD]2/24/2015[/TD]
[TD]Sr. Acct. Supervisor[/TD]
[TD]Smith, Jane[/TD]
[/TR]
[TR]
[TD]2/10/2015[/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[/TR]
[TR]
[TD]2/24/2015[/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[/TR]
[TR]
[TD]3/25/2015[/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?

[TABLE="width: 901"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Invoice Date[/TD]
[TD]Labor Category[/TD]
[TD]Employee Name[/TD]
[TD][/TD]
[TD]Labor Category[/TD]
[TD]Employee Name[/TD]
[TD]Invoice Date[/TD]
[/TR]
[TR]
[TD]11/20/2012[/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]11/20/2012[/TD]
[/TR]
[TR]
[TD]12/20/2012[/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]11/20/2012[/TD]
[/TR]
[TR]
[TD]2/26/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]2/26/2014[/TD]
[/TR]
[TR]
[TD]2/26/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]2/26/2014[/TD]
[/TR]
[TR]
[TD]4/22/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]2/26/2014[/TD]
[/TR]
[TR]
[TD]8/22/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]8/22/2013[/TD]
[/TR]
[TR]
[TD]9/24/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]8/22/2013[/TD]
[/TR]
[TR]
[TD]10/24/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]8/22/2013[/TD]
[/TR]
[TR]
[TD]4/30/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]4/30/2014[/TD]
[/TR]
[TR]
[TD]6/24/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]4/30/2014[/TD]
[/TR]
[TR]
[TD]10/27/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]4/30/2014[/TD]
[/TR]
[TR]
[TD]10/6/2014[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]10/6/2014[/TD]
[/TR]
[TR]
[TD]10/8/2014[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]10/6/2014[/TD]
[/TR]
[TR]
[TD]12/1/2014[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]8/22/2013[/TD]
[/TR]
[TR]
[TD]3/28/2016[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]10/6/2014[/TD]
[/TR]
[TR]
[TD]4/21/2016[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]10/6/2014[/TD]
[/TR]
[TR]
[TD]5/31/2016[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD][/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD="align: right"]10/6/2014[/TD]
[/TR]
[TR]
[TD]2/24/2015[/TD]
[TD]Sr. Acct. Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD][/TD]
[TD]Sr. Acct. Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD="align: right"]2/24/2015[/TD]
[/TR]
[TR]
[TD]2/10/2015[/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD][/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD="align: right"]2/10/2015[/TD]
[/TR]
[TR]
[TD]2/24/2015[/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD][/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD="align: right"]2/10/2015[/TD]
[/TR]
[TR]
[TD]3/25/2015[/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD][/TD]
[TD]Account Supervisor[/TD]
[TD]Smith, Jane[/TD]
[TD="align: right"]2/10/2015[/TD]
[/TR]
</tbody>[/TABLE]

 

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


[TABLE="width: 736"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Invoice Date[/TD]
[TD]Labor Category[/TD]
[TD]Employee Name[/TD]
[TD]Contract Number[/TD]
[TD]Hourly Rate[/TD]
[/TR]
[TR]
[TD]11/20/2012[/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[TD]11[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD]12/20/2012[/TD]
[TD]Executive Accountant I[/TD]
[TD]Doe, Jack[/TD]
[TD]11[/TD]
[TD]23.00[/TD]
[/TR]
[TR]
[TD]2/26/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD]22[/TD]
[TD]30.00[/TD]
[/TR]
[TR]
[TD]2/26/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD]22[/TD]
[TD]29.00[/TD]
[/TR]
[TR]
[TD]4/22/2014[/TD]
[TD]Acct. Executive[/TD]
[TD]Doe, Jack[/TD]
[TD]22[/TD]
[TD]30.00[/TD]
[/TR]
[TR]
[TD]8/22/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD]56[/TD]
[TD]81.00[/TD]
[/TR]
[TR]
[TD]9/24/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD]56[/TD]
[TD]85.00[/TD]
[/TR]
[TR]
[TD]10/24/2013[/TD]
[TD]Executive Accountant II[/TD]
[TD]Doe, Jack[/TD]
[TD]56[/TD]
[TD]85.00[/TD]
[/TR]
[TR]
[TD]4/30/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD]84[/TD]
[TD]41.00[/TD]
[/TR]
[TR]
[TD]6/24/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD]66[/TD]
[TD]44.00[/TD]
[/TR]
[TR]
[TD]10/27/2014[/TD]
[TD]Account Executive II[/TD]
[TD]Doe, Jack[/TD]
[TD]87[/TD]
[TD]44.00[/TD]
[/TR]
[TR]
[TD]10/6/2014[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD]96[/TD]
[TD]22.00[/TD]
[/TR]
[TR]
[TD]10/8/2014[/TD]
[TD]Senior Executive[/TD]
[TD]Doe, Jack[/TD]
[TD]22[/TD]
[TD]23.00[/TD]
[/TR]
</tbody>[/TABLE]