Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value then return result from other column, varying row num
Hello,
I have used the excel discussion group to solve many problems, and now have one that I need to post! I have a report that exports data (actually in text format, but that may not matter) where I need to look down column A and find the cells with "AxxxS" where the xxx are numbers (well, text) in ascending order. In between the "AxxxS" values are cells in column a with various meaningless text data. What I need to find is the maximum value (remember these numbers are exported to this file as text) in column H that is some rows down from the "AxxxS" row. The number of rows down can vary, but it always will be the cell with one row above the next "AxxxS" value. There are over 1000 rows, so I have to pull around 100 values of "AxxxS" and the corresponding number in column H. Also, the number of rows between each "AxxxS" can be as little as two rows, or 15 rows. Lastly, there are other values in column H, but the one I need is the maximum of those values before the next "AxxxS" sequence. I have been trying sumproduct and offset and other combinations, but can't find a solution, hopefully someone can help! Here is what the data looks like. A B --- H 1 A500S 2 othertext 3 othertext 4 othertext 3838 5 A501S 6 othertext 7 othertext 2500 8 A502S 9 othertext 10 othertext 11 othertext 3000 12 A503S 13 othertext 14 othertext 15 othertext 16 othertext 1000 17 A504S 18 othertext 19 othertext 20 othertext 1200 Want to return in columns J and K: A500S 3838 A501S 2500 A502S 3000 A503S 1000 A504S 1200 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value then return result from other column, varying row num
In I1:
=IF(AND(LEFT(A1)="A",RIGHT(A1)="S"),ROW(),"") In J1: =IF(ROW()COUNT(I:I),"",INDEX(A:A,SMALL(I:I,ROW()) )) In K1: =IF(J2="","",OFFSET($H$1,MATCH(J2,A:A,0)-2,)) Select I1:K1, copy down to last row of source data. Minimize/Hide away col I. Cols J & K returns 99.99% desired results. For the last value extracted in col J, do a manual check & overwrite the blank in col K. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Justin" wrote: Hello, I have used the excel discussion group to solve many problems, and now have one that I need to post! I have a report that exports data (actually in text format, but that may not matter) where I need to look down column A and find the cells with "AxxxS" where the xxx are numbers (well, text) in ascending order. In between the "AxxxS" values are cells in column a with various meaningless text data. What I need to find is the maximum value (remember these numbers are exported to this file as text) in column H that is some rows down from the "AxxxS" row. The number of rows down can vary, but it always will be the cell with one row above the next "AxxxS" value. There are over 1000 rows, so I have to pull around 100 values of "AxxxS" and the corresponding number in column H. Also, the number of rows between each "AxxxS" can be as little as two rows, or 15 rows. Lastly, there are other values in column H, but the one I need is the maximum of those values before the next "AxxxS" sequence. I have been trying sumproduct and offset and other combinations, but can't find a solution, hopefully someone can help! Here is what the data looks like. A B --- H 1 A500S 2 othertext 3 othertext 4 othertext 3838 5 A501S 6 othertext 7 othertext 2500 8 A502S 9 othertext 10 othertext 11 othertext 3000 12 A503S 13 othertext 14 othertext 15 othertext 16 othertext 1000 17 A504S 18 othertext 19 othertext 20 othertext 1200 Want to return in columns J and K: A500S 3838 A501S 2500 A502S 3000 A503S 1000 A504S 1200 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value then return result from other column, varying row num
1 A500S
2 othertext 3 othertext =IF(AND(LEFT(A1)="A",RIGHT(A1)="S"),ROW(),"") It might be possible that "othertext" will meet those conditions. All of the "codes" seem to be the same length so you might want to use that as condition: AND(LEN(A1)=5,LEFT(A1)&RIGHT(A1)="AS") AND(LEN(A1)=5,REPLACE(A1,2,3,"")="AS") Those could also fail! -- Biff Microsoft Excel MVP "Max" wrote in message ... In I1: =IF(AND(LEFT(A1)="A",RIGHT(A1)="S"),ROW(),"") In J1: =IF(ROW()COUNT(I:I),"",INDEX(A:A,SMALL(I:I,ROW()) )) In K1: =IF(J2="","",OFFSET($H$1,MATCH(J2,A:A,0)-2,)) Select I1:K1, copy down to last row of source data. Minimize/Hide away col I. Cols J & K returns 99.99% desired results. For the last value extracted in col J, do a manual check & overwrite the blank in col K. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Justin" wrote: Hello, I have used the excel discussion group to solve many problems, and now have one that I need to post! I have a report that exports data (actually in text format, but that may not matter) where I need to look down column A and find the cells with "AxxxS" where the xxx are numbers (well, text) in ascending order. In between the "AxxxS" values are cells in column a with various meaningless text data. What I need to find is the maximum value (remember these numbers are exported to this file as text) in column H that is some rows down from the "AxxxS" row. The number of rows down can vary, but it always will be the cell with one row above the next "AxxxS" value. There are over 1000 rows, so I have to pull around 100 values of "AxxxS" and the corresponding number in column H. Also, the number of rows between each "AxxxS" can be as little as two rows, or 15 rows. Lastly, there are other values in column H, but the one I need is the maximum of those values before the next "AxxxS" sequence. I have been trying sumproduct and offset and other combinations, but can't find a solution, hopefully someone can help! Here is what the data looks like. A B --- H 1 A500S 2 othertext 3 othertext 4 othertext 3838 5 A501S 6 othertext 7 othertext 2500 8 A502S 9 othertext 10 othertext 11 othertext 3000 12 A503S 13 othertext 14 othertext 15 othertext 16 othertext 1000 17 A504S 18 othertext 19 othertext 20 othertext 1200 Want to return in columns J and K: A500S 3838 A501S 2500 A502S 3000 A503S 1000 A504S 1200 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value then return result from other column, varying row
Wonder if the OP is interested in the responses given? In the absence of any
feedback from OP, looks like our efforts here responding to his posting have/will only benefit all others, other than the OP. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value then return result from other column, varying row
Thanks Max! I have been away, so sorry about the late thanks. I was
bothered by the last cell, but found a way to get the last cell information to automatically be shown (the report has cells in the very bottom that are always the same, so I searched for those). It works great, and T.Valko was right in that the returns may find other data, but not in my case. Thanks again! Justin "Max" wrote: In I1: =IF(AND(LEFT(A1)="A",RIGHT(A1)="S"),ROW(),"") In J1: =IF(ROW()COUNT(I:I),"",INDEX(A:A,SMALL(I:I,ROW()) )) In K1: =IF(J2="","",OFFSET($H$1,MATCH(J2,A:A,0)-2,)) Select I1:K1, copy down to last row of source data. Minimize/Hide away col I. Cols J & K returns 99.99% desired results. For the last value extracted in col J, do a manual check & overwrite the blank in col K. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Justin" wrote: Hello, I have used the excel discussion group to solve many problems, and now have one that I need to post! I have a report that exports data (actually in text format, but that may not matter) where I need to look down column A and find the cells with "AxxxS" where the xxx are numbers (well, text) in ascending order. In between the "AxxxS" values are cells in column a with various meaningless text data. What I need to find is the maximum value (remember these numbers are exported to this file as text) in column H that is some rows down from the "AxxxS" row. The number of rows down can vary, but it always will be the cell with one row above the next "AxxxS" value. There are over 1000 rows, so I have to pull around 100 values of "AxxxS" and the corresponding number in column H. Also, the number of rows between each "AxxxS" can be as little as two rows, or 15 rows. Lastly, there are other values in column H, but the one I need is the maximum of those values before the next "AxxxS" sequence. I have been trying sumproduct and offset and other combinations, but can't find a solution, hopefully someone can help! Here is what the data looks like. A B --- H 1 A500S 2 othertext 3 othertext 4 othertext 3838 5 A501S 6 othertext 7 othertext 2500 8 A502S 9 othertext 10 othertext 11 othertext 3000 12 A503S 13 othertext 14 othertext 15 othertext 16 othertext 1000 17 A504S 18 othertext 19 othertext 20 othertext 1200 Want to return in columns J and K: A500S 3838 A501S 2500 A502S 3000 A503S 1000 A504S 1200 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value then return result from other column, varying row
Welcome, good to hear that you got your issue resolved.
(I was just cruising by in the neighbourhood ..) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Justin" wrote in message ... Thanks Max! I have been away, so sorry about the late thanks. I was bothered by the last cell, but found a way to get the last cell information to automatically be shown (the report has cells in the very bottom that are always the same, so I searched for those). It works great, and T.Valko was right in that the returns may find other data, but not in my case. Thanks again! Justin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup for a value in multiple columns and return a result | Excel Discussion (Misc queries) | |||
Lookup 3 columns and return a result from another column | Excel Discussion (Misc queries) | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
lookup value and return result in column to left | Excel Worksheet Functions | |||
I need a Lookup to return more than 1 result | Excel Worksheet Functions |