| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look for similar text and find the largest value and return value
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look for similar text and find the largest value and return va
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look for similar text and find the largest value and return va
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" wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look for similar text and find the largest value and return va
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" wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look for similar text and find the largest value and return va
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
