Need largest number
in [Programming]
|
From: Nadine on 7 Jun 2010 16:23 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. "Rick Rothstein" wrote: > 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. > > -- > Rick (MVP - Excel) > > > > "Nadine" <Nadine(a)discussions.microsoft.com> wrote in message > news:7D7CF581-FBF2-4EC3-90E4-CB45B00C0471(a)microsoft.com... > > That's not working. I get the result #VALUE > > > > "Rick Rothstein" wrote: > > > >> 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) > >> > >> > >> > >> "Nadine" <Nadine(a)discussions.microsoft.com> wrote in message > >> news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1(a)microsoft.com... > >> > 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. > >> > >> . > >> > . >
From: Nadine on 7 Jun 2010 17:50
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. "Rick Rothstein" wrote: > 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. > > -- > Rick (MVP - Excel) > > > > "Nadine" <Nadine(a)discussions.microsoft.com> wrote in message > news:7D7CF581-FBF2-4EC3-90E4-CB45B00C0471(a)microsoft.com... > > That's not working. I get the result #VALUE > > > > "Rick Rothstein" wrote: > > > >> 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) > >> > >> > >> > >> "Nadine" <Nadine(a)discussions.microsoft.com> wrote in message > >> news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1(a)microsoft.com... > >> > 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. > >> > >> . > >> > . > |