Need largest number
in [Programming]
|
From: Nadine on 7 Jun 2010 13:03 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: Rick Rothstein on 7 Jun 2010 13:23 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 15:11 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: Rick Rothstein on 7 Jun 2010 15:33 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 16:22
Sorry about that. User error. "Nadine" wrote: > 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. > > >> > > >> . > > >> > > . > > |