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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look for similar text and find the largest value and return va
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" 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 | |
|
|