Look for similar text and find the largest value and return value

Hello,
What is the formula to look for similar word under KEY and get it to look
for the highest value and REV and retun the RESULT value?
Example below

KEY REV RESULT
a 0 1
b 0 5
c 0 2
b 1 6
c 1 3
b 2 4


want result to be...

a 0 1
b 2 4
c 1 3

Hi!

How many columns is this data in?

a 0 1
b 0 5
c 0 2
b 1 6
c 1 3
b 2 4

Biff

"Quan" <Quan@discussions.microsoft.com> wrote in message
news:433D92D8-2516-4B7C-8731-7C6ED981CC79@microsoft.com...
> Hello,
> What is the formula to look for similar word under KEY and get it to look
> for the highest value and REV and retun the RESULT value?
> Example below
>
> KEY REV RESULT
> a 0 1
> b 0 5
> c 0 2
> b 1 6
> c 1 3
> b 2 4
>
>
> want result to be...
>
> a 0 1
> b 2 4
> c 1 3
>

In this example, 3 columns with headings: Key, Rev, and Result.

I my worksheet it will have 15 columns.

"Biff" wrote:

> Hi!
>
> How many columns is this data in?
>
> a 0 1
> b 0 5
> c 0 2
> b 1 6
> c 1 3
> b 2 4
>
> Biff
>
> "Quan" <Quan@discussions.microsoft.com> wrote in message
> news:433D92D8-2516-4B7C-8731-7C6ED981CC79@microsoft.com...
> > Hello,
> > What is the formula to look for similar word under KEY and get it to look
> > for the highest value and REV and retun the RESULT value?
> > Example below
> >
> > KEY REV RESULT
> > a 0 1
> > b 0 5
> > c 0 2
> > b 1 6
> > c 1 3
> > b 2 4
> >
> >
> > want result to be...
> >
> > a 0 1
> > b 2 4
> > c 1 3
> >
>
>
>

Ok.....

with this data in A2:C7 -

a 0 1
b 0 5
c 0 2
b 1 6
c 1 3
b 2 4

A10 = A
A11 = B
A12 = C

Enter this formula in B10 using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=MAX(IF(A$2:A$7=A10,B$2:B$7))

Enter this formula in C10 using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=INDEX(C$2:C$7,MATCH(1,(A$2:A$7=A10)*(B$2:B$7=B10),0))

Select both B10 and C10 and copy down to row 12.

Results will be:

A 0 1
B 2 4
C 1 3

Biff

"Quan" <Quan@discussions.microsoft.com> wrote in message
news:6BAA8555-D8DA-45BB-89D7-9B690BEB637A@microsoft.com...
> In this example, 3 columns with headings: Key, Rev, and Result.
>
> I my worksheet it will have 15 columns.
>
> "Biff" wrote:
>
>> Hi!
>>
>> How many columns is this data in?
>>
>> a 0 1
>> b 0 5
>> c 0 2
>> b 1 6
>> c 1 3
>> b 2 4
>>
>> Biff
>>
>> "Quan" <Quan@discussions.microsoft.com> wrote in message
>> news:433D92D8-2516-4B7C-8731-7C6ED981CC79@microsoft.com...
>> > Hello,
>> > What is the formula to look for similar word under KEY and get it to
>> > look
>> > for the highest value and REV and retun the RESULT value?
>> > Example below
>> >
>> > KEY REV RESULT
>> > a 0 1
>> > b 0 5
>> > c 0 2
>> > b 1 6
>> > c 1 3
>> > b 2 4
>> >
>> >
>> > want result to be...
>> >
>> > a 0 1
>> > b 2 4
>> > c 1 3
>> >
>>
>>
>>

Biff, Thank you.

What if I'm going to have thousands of rows and more will be added and I
will not know all the Keys to create a list (starting in A10) for it to
reference in advance. Is there a way to automate the process and combine all
the same Keys into one and then compare the largest revision and return the
revision and results as you have it? I could manually copy Column A and sort
and delete all similary Keys create the list first before doing the your
operation but this would be very time consuming each time.

Thanks again for your help.

Quan

"Biff" wrote:

> Ok.....
>
> with this data in A2:C7 -
>
> a 0 1
> b 0 5
> c 0 2
> b 1 6
> c 1 3
> b 2 4
>
> A10 = A
> A11 = B
> A12 = C
>
> Enter this formula in B10 using the key combination of CTRL,SHIFT,ENTER (not
> just ENTER):
>
> =MAX(IF(A$2:A$7=A10,B$2:B$7))
>
> Enter this formula in C10 using the key combination of CTRL,SHIFT,ENTER (not
> just ENTER):
>
> =INDEX(C$2:C$7,MATCH(1,(A$2:A$7=A10)*(B$2:B$7=B10),0))
>
> Select both B10 and C10 and copy down to row 12.
>
> Results will be:
>
> A 0 1
> B 2 4
> C 1 3
>
> Biff
>
> "Quan" <Quan@discussions.microsoft.com> wrote in message
> news:6BAA8555-D8DA-45BB-89D7-9B690BEB637A@microsoft.com...
> > In this example, 3 columns with headings: Key, Rev, and Result.
> >
> > I my worksheet it will have 15 columns.
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> How many columns is this data in?
> >>
> >> a 0 1
> >> b 0 5
> >> c 0 2
> >> b 1 6
> >> c 1 3
> >> b 2 4
> >>
> >> Biff
> >>
> >> "Quan" <Quan@discussions.microsoft.com> wrote in message
> >> news:433D92D8-2516-4B7C-8731-7C6ED981CC79@microsoft.com...
> >> > Hello,
> >> > What is the formula to look for similar word under KEY and get it to
> >> > look
> >> > for the highest value and REV and retun the RESULT value?
> >> > Example below
> >> >
> >> > KEY REV RESULT
> >> > a 0 1
> >> > b 0 5
> >> > c 0 2
> >> > b 1 6
> >> > c 1 3
> >> > b 2 4
> >> >
> >> >
> >> > want result to be...
> >> >
> >> > a 0 1
> >> > b 2 4
> >> > c 1 3
> >> >
> >>
> >>
> >>
>
>
>

If you're going to have 1000's of rows then you probably need to do this
with VBA procedures. I can't help you with that.

Biff

"Quan" <Quan@discussions.microsoft.com> wrote in message
news:8C1F942F-60C9-48F1-AE9F-39453254C8A0@microsoft.com...
> Biff, Thank you.
>
> What if I'm going to have thousands of rows and more will be added and I
> will not know all the Keys to create a list (starting in A10) for it to
> reference in advance. Is there a way to automate the process and combine
> all
> the same Keys into one and then compare the largest revision and return
> the
> revision and results as you have it? I could manually copy Column A and
> sort
> and delete all similary Keys create the list first before doing the your
> operation but this would be very time consuming each time.
>
> Thanks again for your help.
>
> Quan
>
> "Biff" wrote:
>
>> Ok.....
>>
>> with this data in A2:C7 -
>>
>> a 0 1
>> b 0 5
>> c 0 2
>> b 1 6
>> c 1 3
>> b 2 4
>>
>> A10 = A
>> A11 = B
>> A12 = C
>>
>> Enter this formula in B10 using the key combination of CTRL,SHIFT,ENTER
>> (not
>> just ENTER):
>>
>> =MAX(IF(A$2:A$7=A10,B$2:B$7))
>>
>> Enter this formula in C10 using the key combination of CTRL,SHIFT,ENTER
>> (not
>> just ENTER):
>>
>> =INDEX(C$2:C$7,MATCH(1,(A$2:A$7=A10)*(B$2:B$7=B10),0))
>>
>> Select both B10 and C10 and copy down to row 12.
>>
>> Results will be:
>>
>> A 0 1
>> B 2 4
>> C 1 3
>>
>> Biff
>>
>> "Quan" <Quan@discussions.microsoft.com> wrote in message
>> news:6BAA8555-D8DA-45BB-89D7-9B690BEB637A@microsoft.com...
>> > In this example, 3 columns with headings: Key, Rev, and Result.
>> >
>> > I my worksheet it will have 15 columns.
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> How many columns is this data in?
>> >>
>> >> a 0 1
>> >> b 0 5
>> >> c 0 2
>> >> b 1 6
>> >> c 1 3
>> >> b 2 4
>> >>
>> >> Biff
>> >>
>> >> "Quan" <Quan@discussions.microsoft.com> wrote in message
>> >> news:433D92D8-2516-4B7C-8731-7C6ED981CC79@microsoft.com...
>> >> > Hello,
>> >> > What is the formula to look for similar word under KEY and get it to
>> >> > look
>> >> > for the highest value and REV and retun the RESULT value?
>> >> > Example below
>> >> >
>> >> > KEY REV RESULT
>> >> > a 0 1
>> >> > b 0 5
>> >> > c 0 2
>> >> > b 1 6
>> >> > c 1 3
>> >> > b 2 4
>> >> >
>> >> >
>> >> > want result to be...
>> >> >
>> >> > a 0 1
>> >> > b 2 4
>> >> > c 1 3
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>