Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to find relative position - is there a better way?
I have a large table of transactions that includes Project Number,
Transaction Date and Project Stage. The data ranges are named Projects, Dates and Stages. The lookup value is a specific project number that is stored in a single cell, say "A1". The data can be sorted in random order which limits some approaches. I've created a somewhat complex array formula that will return the relative position number for the row that has the largest date value for rows that match project. (If multiple rows have the same date, it returns the last such row.) I can use this in an INDEX function to pull the most recent stage. Formula to return the relative position (row with in a range): {=MAX(((Projects=A1)*Dates=MAX((Projects=A1)*Dates ))*ROW(INDIRECT("A1:A"&ROWS(Projects))))} My array formula seems overly complex but it works so I'm hoping there's a better way. Any ideas? - John Michl www.johnmichl.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to find relative position - is there a better way?
This looks simpler to me
=MATCH(1,(Projects=A1)*(MAX(IF(Projects=A1,Dates)) =Dates),0) still an arrauy formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John Michl" wrote in message ps.com... I have a large table of transactions that includes Project Number, Transaction Date and Project Stage. The data ranges are named Projects, Dates and Stages. The lookup value is a specific project number that is stored in a single cell, say "A1". The data can be sorted in random order which limits some approaches. I've created a somewhat complex array formula that will return the relative position number for the row that has the largest date value for rows that match project. (If multiple rows have the same date, it returns the last such row.) I can use this in an INDEX function to pull the most recent stage. Formula to return the relative position (row with in a range): {=MAX(((Projects=A1)*Dates=MAX((Projects=A1)*Dates ))*ROW(INDIRECT("A1:A"&ROWS(Projects))))} My array formula seems overly complex but it works so I'm hoping there's a better way. Any ideas? - John Michl www.johnmichl.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to find relative position - is there a better way?
Thanks Bob. That does help. It is much cleaner than my approach.
However, in case of a tie (i.e. two identical dates for the matching project, it pulls the position of the first record not the last. My complex formula does pull the last record but given that it is highly unlikely that the a project would have two different stages on a given day, I think I'll go with the more simple approach. Thanks. - John Michl On Oct 5, 6:09 pm, "Bob Phillips" wrote: This looks simpler to me =MATCH(1,(Projects=A1)*(MAX(IF(Projects=A1,Dates)) =Dates),0) still an arrauy formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John Michl" wrote in message ps.com... I have a large table of transactions that includes Project Number, Transaction Date and Project Stage. The data ranges are named Projects, Dates and Stages. The lookup value is a specific project number that is stored in a single cell, say "A1". The data can be sorted in random order which limits some approaches. I've created a somewhat complex array formula that will return the relative position number for the row that has the largest date value for rows that match project. (If multiple rows have the same date, it returns the last such row.) I can use this in an INDEX function to pull the most recent stage. Formula to return the relative position (row with in a range): {=MAX(((Projects=A1)*Dates=MAX((Projects=A1)*Dates ))*ROW(INDIRECT("A1:A"&RO*WS(Projects))))} My array formula seems overly complex but it works so I'm hoping there's a better way. Any ideas? - John Michl www.johnmichl.com- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array to find relative position - is there a better way?
I figured out an alternative that will return the position of the last
match instead of the first. {=MATCH(1,1/((Projects=$A$1)*(MAX(IF(Projects=$A$1,Dates))=Dat es)), 1)} (entered as an array formula) Dividing the array inside this MATCH formula into 1 (i.e. 1/(rest of formula)) creates an array of #DIV/0! values where the original array value was 0. The remaining values will be 1 since (1/1 = 1). Since MATCH ignores error values, the only values evaluated in the formula are the ones that match the criteria. The Match type of 1 forces the formula to return the position of last matching value. Thanks for the help Bob. - John Michl http://excelguru.johnmichl.com coming soon. On Oct 8, 9:55 am, John Michl wrote: Thanks Bob. That does help. It is much cleaner than my approach. However, in case of a tie (i.e. two identical dates for the matching project, it pulls the position of the first record not the last. My complex formula does pull the last record but given that it is highly unlikely that the a project would have two different stages on a given day, I think I'll go with the more simple approach. Thanks. - John Michl On Oct 5, 6:09 pm, "Bob Phillips" wrote: This looks simpler to me =MATCH(1,(Projects=A1)*(MAX(IF(Projects=A1,Dates)) =Dates),0) still an arrauy formula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John Michl" wrote in message ups.com... I have a large table of transactions that includes Project Number, Transaction Date and Project Stage. The data ranges are named Projects, Dates and Stages. The lookup value is a specific project number that is stored in a single cell, say "A1". The data can be sorted in random order which limits some approaches. I've created a somewhat complex array formula that will return the relative position number for the row that has the largest date value for rows that match project. (If multiple rows have the same date, it returns the last such row.) I can use this in an INDEX function to pull the most recent stage. Formula to return the relative position (row with in a range): {=MAX(((Projects=A1)*Dates=MAX((Projects=A1)*Dates ))*ROW(INDIRECT("A1:A"&RO**WS(Projects))))} My array formula seems overly complex but it works so I'm hoping there's a better way. Any ideas? - John Michl www.johnmichl.com-Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative position of Employees | Excel Worksheet Functions | |||
Excel Formula using relative position of cells in two different worksheets | Excel Discussion (Misc queries) | |||
Excel Formula using relative position of cells in two different worksheets | Excel Worksheet Functions | |||
Visual Basic Macros, relative position | Charts and Charting in Excel | |||
Relative Cell position NOT working with or without macro | Excel Discussion (Misc queries) |