combining countif and match

[kcmuppet]

In the example below, is there a simple way to get column B values to be populated with the highest value from column C which has the same value in column A.?

So for example the formula in B5 would go something like...
if the value in column A already is not the first in column A, then for all the rows that have the same value ("JS" in this case), find the highest value (most recent date) in column C and put it in here.

I've been struggling with countif & match, but not really getting very far. Can anybody help?

Rocksharp Books 05-06 v1-60 -reimbursements.xls
	A	B	C	D
2	Employee	Lastreimbursed	Reimburseto(date)	AmountDue
3	KC	01-Apr-04	31-Mar-05	3,092.47
4	JS	08-Apr-04	28-Feb-05
5	JS	28-Feb-05
Reimbursements

 

[NBVC]

I wasn't sure if you wanted the date of the last reimbursed date for everyone as a group or specific to that individual, but here are 2 solutions. Apply the one that best fits your needs.

The solution in the green area assumes you want the last reimbursed date for all as a group.

The solution in the blue area assumes you want to get the last reimbursed date for that individual.

Not sure what date you want for an employee not already in the list so I just returned a blank (""). You can replace that with the cell reference that contains the date you want to be populated, or if todays day, replace with Today().

Do not, however, overwrite the formula or it will be lost from that cell.

Formula in B2 is =IF($A1="Employee","",IF(COUNTIF($A1:$A$2,$A2)>0,LOOKUP(9.99999999999999E+307,$C1:$C$2),""))

Formula in F2 is =IF($E1="Employee","",IF(COUNTIF($E1:$E$2,$E2)>0,SUMPRODUCT(MAX(($E1:$E$2=E2)*$G1:$G$2)),""))

Whichever, you choose, adjust to suit your column lettes and row numbers.

Then copy down the formula to bottom of list.

Book2
	A	B	C	D	E	F	G
1	Employee	Last Reimbursed	Reimburse to (date)		Employee	Last Reimbursed	Reimburse to (date)
2	KC		31-Mar-05		KC		31-Mar-05
3	js		28-Feb-05		JS		28-Feb-05
4	JS	28-Feb-05			JS	28-Feb-05
5	JS	28-Feb-05			JS	28-Feb-05
6	JS	28-Feb-05			JS	28-Feb-05
7	JS	28-Feb-05			JS	28-Feb-05
8	JS	28-Feb-05			JS	28-Feb-05
9	JS	28-Feb-05			JS	28-Feb-05
10	JS	28-Feb-05			JS	28-Feb-05
11	KC	28-Feb-05			KC	31-Mar-05
12	NC				NC
Sheet2

 

[kcmuppet]

I wasn't sure if you wanted the date of the last reimbursed date for everyone as a group or specific to that individual

For the specific individual, thankyou - that's just was I was looking for.

Not sure what date you want for an employee not already in the list so I just returned a blank (""). You can replace that with the cell reference that contains the date you want to be populated, or if todays day, replace with Today(). Do not, however, overwrite the formula or it will be lost from that cell.

That date will need to be entered manually I think, for that cell. It seems it doesn't matter if the formula is overwritten for just that cell because the it will still exist in the cells below, right?

Out of interest, in the first solution (as if it were for all employees), could you explain if you have time how the lookup works. I don't understand the siginficance of that big value.

Thanks for your help with this.

 

[NBVC]

That's the largest number Excel can handle, so the lookup looks for that big number in your table and when it doesn't find it, it returns the closest match (ie. the largest number) to that without going over (which should never happen).

Here is a link that explains it well.

http://www.mrexcel.com/board2/viewtopic.php?t=105725