Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need largest number
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need largest number
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" wrote in message ... 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need largest number
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" wrote in message ... 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" wrote in message ... 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need largest number
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" wrote in message ... 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" wrote in message ... 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. . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need largest number
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" wrote in message ... 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" wrote in message ... 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. . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need largest number
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" wrote in message ... 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" wrote in message ... 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Folder with largest number | Excel Discussion (Misc queries) | |||
Largest number 2 | Excel Worksheet Functions | |||
Largest number | Excel Worksheet Functions | |||
Largest Prime Number | Excel Discussion (Misc queries) | |||
Identifying the largest number | Excel Programming |