Looking up maximum value based on another value
 

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?

Looking up maximum value based on another value
 

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
$1:D99))+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

On Apr 27, 11:26 am, nice_guy_but
wrote:
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?

Looking up maximum value based on another value
 

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" wrote:

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
$1:D99))+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

On Apr 27, 11:26 am, nice_guy_but
wrote:
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?

Looking up maximum value based on another value
 

Glad it worked for you - thanks for feeding back.

Pete

On May 2, 10:44 am, nice_guy_but
wrote:
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" wrote:
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
$1:D99))+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

On Apr 27, 11:26 am, nice_guy_but
wrote:
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?- Hide quoted text -

- Show quoted text -