Need largest number

[Nadine]

I have a file in Excel 2003. In 1 column I have a numeric field. In the
next column I have a 2 digit apha field. In the third column I have another
numeric field. For each row I need a formula in the 4th column.

All columns can have duplicate numbers/characters. What I need to do is
find the largest number if column 3 for the unique combination of columns 1
and 2.

Example:
Col A Col B Col C Col D (to be reult is shown - need formula)
12345 AA 1 5
12345 AA 1 5
12345 AA 2 5
98765 BA 2 2
98765 BA 1 2
12345 AA 5 5
98765 BA 2 2

Thank you.

 

[Rick Rothstein]

Assuming your data starts in Row 1, put this formula in D1 and copy it
down...

=SUMPRODUCT(MAX((A$1:A$1000&B$1:B$1000=A1&B1)*C$1:C$1000))

Change all the 1000's to a row number that will be larger than the largest
row number you ever expect to put data in.

 

[Nadine]

That's not working. I get the result #VALUE

Assuming your data starts in Row 1, put this formula in D1 and copy it
down...

=SUMPRODUCT(MAX((A$1:A$1000&B$1:B$1000=A1&B1)*C$1:C$1000))

Change all the 1000's to a row number that will be larger than the largest
row number you ever expect to put data in.

--
Rick (MVP - Excel)





.

 

[Rick Rothstein]

The formula works here for the data you provided to us, so I have to ask to
be sure... did you copy/paste my formula into your worksheet or did you
attempt to type it longhand (possibly introducing a typing error in the
process)? If you copy/pasted it, then I have to conclude your data is
different in some way from what you posted. Again, if you copy/pasted the
formula in and it is not working, then you can send me your workbook and
I'll see if I can spot what the problem is. If you do send it to me, make
sure to remove the NO.SPAM stuff from my address.

 

[Nadine]

Sorry about that. User error.

I copied and pasted it this time and here's the result:
12345 AA 1 1
12345 AA 1 5
12345 AA 2 5
98765 BA 2 2
98765 BA 1 2
12345 AA 5 5
98765 BA 2 2

The first row is wrong as it should also be 5 just like the one below it.
It's almost there. Thanks so much.

 

[Nadine]

I copied and pasted it this time and here's the result:
12345 AA 1 1
12345 AA 1 5
12345 AA 2 5
98765 BA 2 2
98765 BA 1 2
12345 AA 5 5
98765 BA 2 2

The first row is wrong as it should also be 5 just like the one below it.
It's almost there. Thanks so much.

 

[Nadine]

Rick,

I sent you the file in hopes you can help again. It doesn't seem to work in
the actual file although it works in the test file of the sample I gave.
THanks.