Formulae help

[MOB]

1001 31/12/2011
1001 31/12/2011
1002 31/12/2011
1001 31/01/2012
1003 31/01/2012
1004 31/01/2012
1002 28/02/2012
1004 28/02/2012

I am after a couple of formulae based on the above data - I would enter say 1001 in cell C3 and require the following;

- for all instances of 1001, are the dates all exactly the same (Y/N)
- what is the latest date (highest value) for 1001

Any help would be great

 

[Andrew Poulsom]

Try:

=IF(SUMPRODUCT(--(A1:A8=C3),--(B1:B8=VLOOKUP(C3,A1:B8,2,FALSE)))=COUNTIF(A1:A8,C3),"Y","N")

=MAX(IF(A1:A8=C3,B1:B8))

The second formula is an array formula which must be confirmed with Ctrl+Shift+Enter. If correctly entered Excel will surround the formula with curly braces {}.

 

[MOB]

Try:

=IF(SUMPRODUCT(--(A1:A8=C3),--(B1:B8=VLOOKUP(C3,A1:B8,2,FALSE)))=COUNTIF(A1:A8,C3),"Y","N")

=MAX(IF(A1:A8=C3,B1:B8))

The second formula is an array formula which must be confirmed with Ctrl+Shift+Enter. If correctly entered Excel will surround the formula with curly braces {}.

Superb, thanks!!