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

I would be willing to try to assist with a VBA solution to the problem.

Rather than working with the 3 columns of simulated data, I'd prefer to
start off with at least a few rows of something that at least looks like what
will be worked with in the real world. The more the better in fact.

Since we're talking about 15 columns of information, I would also need
specific instruction as to what constitutes the "highest value" - just a
value in one other column or more than that somehow?

If you'd like me to try, reply to this message so others will know that your
issue is being dealt with and get in touch with me via email at jlatham @
jlathamsite .com (take out the spaces and you have the email). An .xls file
with sample data attached to the email would be great.

"Biff" wrote:

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