Need largest number
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.
|
0 |
|
Utf
6/7/2010 5:03:28 PM
excel.programming
6508 articles. 
2 followers.
6 Replies
2534 Views
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@discussions.microsoft.com> wrote in message news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1@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.
|
|
0 |
|
Rick
6/7/2010 5:23:57 PM
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@discussions.microsoft.com> wrote in message > news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1@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. > > . >
|
|
0 |
|
Utf
6/7/2010 7:11:05 PM
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@discussions.microsoft.com> wrote in message news:7D7CF581-FBF2-4EC3-90E4-CB45B00C0471@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@discussions.microsoft.com> wrote in message >> news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1@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. >> >> . >>
|
|
0 |
|
Rick
6/7/2010 7:33:00 PM
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@discussions.microsoft.com> wrote in message > > news:7D7CF581-FBF2-4EC3-90E4-CB45B00C0471@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@discussions.microsoft.com> wrote in message > > >> news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1@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. > > >> > > >> . > > >> > > . > >
|
|
0 |
|
Utf
6/7/2010 8:22:58 PM
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@discussions.microsoft.com> wrote in message > news:7D7CF581-FBF2-4EC3-90E4-CB45B00C0471@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@discussions.microsoft.com> wrote in message > >> news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1@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. > >> > >> . > >> > . >
|
|
0 |
|
Utf
6/7/2010 8:23:05 PM
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@discussions.microsoft.com> wrote in message > news:7D7CF581-FBF2-4EC3-90E4-CB45B00C0471@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@discussions.microsoft.com> wrote in message > >> news:14AAC184-B396-4F0E-AE3F-FC3D96CAB7D1@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. > >> > >> . > >> > . >
|
|
0 |
|
Utf
6/7/2010 9:50:55 PM
Similar Artilces: