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. Follow

6 Replies
2534 Views

Similar Articles

[PageSpeed] 15

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
Reply:

Similar Artilces: