Looking up maximum value based on another value

[nice_guy_but]

I'm using Excel 2003 to maintain a correspondence register. What I'd like to
be able to do is whenever a new piece of correspondence comes in, that when I
assign it a particular file reference, it automatically assigns the next
serial number in that particular sequence.

For example, if a letter comes in for Accounts, in column A I'd have the
date received, in B the overall file reference, then in C I would enter
"AC/". What I'd then want is for Excel to search all the instances of "AC/"
in column C in the rows above the new one, find the corresponding maximum
value in column D, then add 1 to it to give the next serial number.

So for the first instance, the cells would be:

21/3/07 123456/4/2/… AC/ 27

and in the next row, when I enter

27/4/07 123456/4/2/… AC/

I want "28" to appear in column D, and so on.

The closest I've come to a solution is below:

=IF($C3="","",1+VLOOKUP($C3,$C$2:$D2,2,FALSE))

but this doesn't find the maximum value, only the first value, and if I
remove the "FALSE" from the formula, then if I enter a different reference in
column C then it will take the next serial number in D regardless of what's
in C. Is there a way of manipulating VLOOKUP to this end, or is there
another combination of formulae I could try?

 

[Pete_UK]

Assuming you are entering data into row 100, put this array* formula
in D100:

=IF(C100="",0,IF(COUNTIF(C$1:C99,C100)=0,1,MAX(IF(C$1:C99=C100,D
$199))+1))

*As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) instead of
the usual ENTER to commit the formula. If you do this correcly then
Excel will wrap the formula in curly braces { } when viewed in the
formula bar - you must not type these yourself.

The formula will return 0 if there is nothing in the cell in column C,
and if this is the first entry for a particular code it will return 1,
otherwise it will add one on to the highest count for the code in
column C. If you have a header row you might like to change C$1 and D
$1 to C$2 and D$2 respectively.

Copy the formula down (and up if necessary) for as many items as you
think you will need.

Hope this helps.

Pete

 

[nice_guy_but]

That's done the trick. Thanks so much for taking the time to answer, it was
doing my head in trying to figure it out!

Greg

 

[Pete_UK]

Glad it worked for you - thanks for feeding back.

Pete