Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
I am needing some help on this situation. I have the data structured
in this way: A B C Res ID Cost Activity Date 1 1234 $20 1/1/2010 2 1234 $25 1/20/2010 3 7432 $15 2/2/2010 4 2574 $45 10/1/2009 5 7432 $65 1/2/2010 It shows by resource (Col A), Hourly Cost (Col B), and Activity date (Col C). Then I have a second sheet, with this format: A B C Res ID Cost Effective Date 1 1234 $15 12/30/2009 2 1234 $18 1/18/2010 3 7432 $12 1/31/2010 4 2574 $43 9/29/2009 5 7432 $67 12/31/2009 Same format, but it shows the standard cost. Effective Date represents the day when this cost becomes available. What I am trying to do is in the first sheet, add a column with the current Cost (Sheet 2) at the moment of the transaction. Example: Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real cost coming from sheet 2 is $15 (because it is since 30/12/2009 to 1/18/2010, when a new cost becomes effective). Sheet 2 can contain two, three, four... x numbers of times the same resource (with different effective date, of course). Does somebody know how to calculate this??? In the meantime I am trying with Index, Match, Sumproduct, Lookup....... Thanks! Cecilia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
Look at Chip Pearson's website. 'Excel Redirect' (http://www.cpearson.com) I think you also want to look at the effective data to be the data which is closest to the activity date since the price may change a couple of times. I cn easily do this with a macro but it appears you are looking for a formula solution? -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=190056 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
Can you sort the second table starting with =TODAY() and running backwards?
If so searching for the Activity Date within the Effective Date range will return a value from MATCH that provides a starting row/date for the subsequent Resource search. You could use CONCATENATE to produce a string representing the range to be searched, eg =CONCATENATE("B"&(16+F3)&":C21") (where F3 is the result of the first MATCH or the function itself). Finally VLOOKUP, referencing this string using INDIRECT() and set to return the contents one column to the right of the ResID, would give the cost. "Chechu" wrote: I am needing some help on this situation. I have the data structured in this way: A B C Res ID Cost Activity Date 1 1234 $20 1/1/2010 2 1234 $25 1/20/2010 3 7432 $15 2/2/2010 4 2574 $45 10/1/2009 5 7432 $65 1/2/2010 It shows by resource (Col A), Hourly Cost (Col B), and Activity date (Col C). Then I have a second sheet, with this format: A B C Res ID Cost Effective Date 1 1234 $15 12/30/2009 2 1234 $18 1/18/2010 3 7432 $12 1/31/2010 4 2574 $43 9/29/2009 5 7432 $67 12/31/2009 Same format, but it shows the standard cost. Effective Date represents the day when this cost becomes available. What I am trying to do is in the first sheet, add a column with the current Cost (Sheet 2) at the moment of the transaction. Example: Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real cost coming from sheet 2 is $15 (because it is since 30/12/2009 to 1/18/2010, when a new cost becomes effective). Sheet 2 can contain two, three, four... x numbers of times the same resource (with different effective date, of course). Does somebody know how to calculate this??? In the meantime I am trying with Index, Match, Sumproduct, Lookup....... Thanks! Cecilia . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
On Mar 24, 6:06*am, joel wrote:
Look at Chip Pearson's website. * 'Excel Redirect' (http://www.cpearson.com) I think you also want to look at the effective data to be the data which is closest to the activity date since the price may change a couple of times. *I cn easily do this with a macro but it appears you are looking for a formula solution? -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=190056 http://www.thecodecage.com/forumz Joel, thanks a lot for your time on this. The effective date indicates when the new cost becomes available. Thus in the first sheet, I need to lookup, for resource XX, the effective cost during the date where the activity has been made. I have no problem with the idea of a macro!! I just started with a formula, since I might need to explain this to other people, and you know, regular Excel users prefer to see how numbers are driven (even if they don't understand the formula behind). But again, no problem with a macro. I'll post in pbart answer what I habe been doing so far, might help. THANKS a lot! Cecilia |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
pbart,
Thanks a lot for your help on this. I understand your suggestion, except when you say "will return a value from MATCH that provides a starting row/date for the subsequent Resource search". At some point I think that the formula I started to work uses some similar approach. This is how it looks like so far: INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE DATE"<="ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the resource""ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES ID"="Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective Date&ResID)",0)) The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet 2, sorted by Res ID, Eff Date, then with a serie of If I can determine the end date for the effective rate. I know, I know, this is confusing... but looks reasonable??? Still need to work on the N/A error handling, but that's not complex. THANKS! Cecilia On Mar 24, 7:10*pm, pbart wrote: Can you sort the second table starting with =TODAY() and running backwards? If so searching for the Activity Date within the Effective Date range will return a value from MATCH that provides a starting row/date for the subsequent Resource search. You could use CONCATENATE to produce a string representing the range to be searched, eg =CONCATENATE("B"&(16+F3)&":C21") (where F3 is the result of the first MATCH or the function itself). *Finally VLOOKUP, referencing this string using INDIRECT() and set to return the contents one column to the right of the ResID, would give the cost. "Chechu" wrote: I am needing some help on this situation. I have the data structured in this way: * *A * * * B * * * C * *Res ID *Cost * *Activity Date 1 *1234 * * $20 * *1/1/2010 2 *1234 * * $25 * *1/20/2010 3 *7432 * * $15 * *2/2/2010 4 *2574 * * $45 * *10/1/2009 5 *7432 * * $65 * *1/2/2010 It shows by resource (Col A), Hourly Cost (Col B), and Activity date (Col C). Then I have a second sheet, with this format: * *A * * * B * * * C * *Res ID *Cost * *Effective Date 1 *1234 * * $15 * *12/30/2009 2 *1234 * * $18 * *1/18/2010 3 *7432 * * $12 * *1/31/2010 4 *2574 * * $43 * *9/29/2009 5 *7432 * * $67 * *12/31/2009 Same format, but it shows the standard cost. Effective Date represents the day when this cost becomes available. What I am trying to do is in the first sheet, add a column with the current Cost (Sheet 2) at the moment of the transaction. Example: Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real cost coming from sheet 2 is $15 (because it is since 30/12/2009 to 1/18/2010, when a new cost becomes effective). Sheet 2 can contain two, three, four... x numbers of times the same resource (with different effective date, of course). Does somebody know how to calculate this??? In the meantime I am trying with Index, Match, Sumproduct, Lookup....... Thanks! Cecilia .- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
This is the macro solution. My preference is to always use macro instead of a complicated formula. Macros are easier to debug and can be documented. Complicated formulas cannot be documented. Macros also run more effiecently. The macro could be made into a UDF. Sub AddEffectiveDate() Set ActivitySht = Sheets("Sheet1") Set EffectivitySht = Sheets("Sheet2") With ActivitySht .Range("D1") = "Effective Cost" LastRow = .Range("A" & Rows.Count).End(xlUp).Row For RowCount = 2 To LastRow EffectiveDate = 0 ResID = .Range("A" & RowCount) ActivityDate = .Range("C" & RowCount) 'search Effectivity Sheet With EffectivitySht Set c = .Columns("A").Find(what:=ResID, _ LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then FirstAddr = c.Address Do NewEffectiveDate = c.Offset(0, 2) 'only take costs before the activity dates 'and take latest date If NewEffectiveDate <= ActivityDate And _ NewEffectiveDate EffectiveDate Then NewCost = c.Offset(0, 1) EffectiveDate = NewEffectiveDate End If Set c = .Columns("A").FindNext(after:=c) Loop While Not c Is Nothing And c.Address < FirstAddr End If End With If EffectiveDate = 0 Then .Range("D" & RowCount) = "Not Found" Else .Range("D" & RowCount) = NewCost End If Next RowCount End With End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=190056 http://www.thecodecage.com/forumz |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
Chechu
It looks like you now have alternative lines of investigation to follow up: the first using inbuilt functionality of the spreadsheet and the second introducing VBA macros. I do agree with Joel that there are dangers of hiding errors in complicated formulae. If you adopt the formula approach, I would suggest you break the process into small steps using additional columns to hold intermediate results. For example, the first MATCH would tell you how many rows of the second sheet should be ignored because they postdate the activity. That number can be checked for correctness. The CONCATENATE in the next column uses this value of 'number of rows to omit' in order to calculate the part of the range on sheet 2 that remains of interest (the starting cell will be further down the table than the original) and displays the result as a string. Again this is something you can check. Finally a VLOOKUP in the next column can be applied to the range (the INDIRECT(cell) references the string from the previous column) and will find all information relating to the first instance of the ResID. The remaining step before your spreadsheet hits an unsuspecting public is to 'hide your workings' by first hiding the extra columns and, possibly, deselecting headings from the view menu. I hope you are not offended by my suggesting how you might best arrange such a calculation but, like Joel, I do rather shy away from complex formulae. Good luck PBart "Chechu" wrote: pbart, Thanks a lot for your help on this. I understand your suggestion, except when you say "will return a value from MATCH that provides a starting row/date for the subsequent Resource search". At some point I think that the formula I started to work uses some similar approach. This is how it looks like so far: INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE DATE"<="ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the resource""ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES ID"="Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective Date&ResID)",0)) The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet 2, sorted by Res ID, Eff Date, then with a serie of If I can determine the end date for the effective rate. I know, I know, this is confusing... but looks reasonable??? Still need to work on the N/A error handling, but that's not complex. THANKS! Cecilia On Mar 24, 7:10 pm, pbart wrote: Can you sort the second table starting with =TODAY() and running backwards? If so searching for the Activity Date within the Effective Date range will return a value from MATCH that provides a starting row/date for the subsequent Resource search. You could use CONCATENATE to produce a string representing the range to be searched, eg =CONCATENATE("B"&(16+F3)&":C21") (where F3 is the result of the first MATCH or the function itself). Finally VLOOKUP, referencing this string using INDIRECT() and set to return the contents one column to the right of the ResID, would give the cost. "Chechu" wrote: I am needing some help on this situation. I have the data structured in this way: A B C Res ID Cost Activity Date 1 1234 $20 1/1/2010 2 1234 $25 1/20/2010 3 7432 $15 2/2/2010 4 2574 $45 10/1/2009 5 7432 $65 1/2/2010 It shows by resource (Col A), Hourly Cost (Col B), and Activity date (Col C). Then I have a second sheet, with this format: A B C Res ID Cost Effective Date 1 1234 $15 12/30/2009 2 1234 $18 1/18/2010 3 7432 $12 1/31/2010 4 2574 $43 9/29/2009 5 7432 $67 12/31/2009 Same format, but it shows the standard cost. Effective Date represents the day when this cost becomes available. What I am trying to do is in the first sheet, add a column with the current Cost (Sheet 2) at the moment of the transaction. Example: Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real cost coming from sheet 2 is $15 (because it is since 30/12/2009 to 1/18/2010, when a new cost becomes effective). Sheet 2 can contain two, three, four... x numbers of times the same resource (with different effective date, of course). Does somebody know how to calculate this??? In the meantime I am trying with Index, Match, Sumproduct, Lookup....... Thanks! Cecilia .- Hide quoted text - - Show quoted text - . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
On Mar 25, 9:54*am, pbart wrote:
Chechu It looks like you now have alternative lines of investigation to follow up: the first using inbuilt functionality of the spreadsheet and the second introducing VBA macros. I do agree with Joel that there are dangers of hiding errors in complicated formulae. *If you adopt the formula approach, I would suggest you break the process into small steps using additional columns to hold intermediate results. For example, the first MATCH would tell you how many rows of the second sheet should be ignored because they postdate the activity. *That number can be checked for correctness. The CONCATENATE in the next column uses this value of 'number of rows to omit' in order to calculate the part of the range on sheet 2 that remains of interest (the starting cell will be further down the table than the original) and displays the result as a string. *Again this is something you can check. Finally a VLOOKUP in the next column can be applied to the range (the INDIRECT(cell) references the string from the previous column) and will find all information relating to the first instance of the ResID. * The remaining step before your spreadsheet hits an unsuspecting public is to 'hide your workings' by first hiding the extra columns and, possibly, deselecting headings from the view menu. I hope you are not offended by my suggesting how you might best arrange such a calculation but, like Joel, I do rather shy away from complex formulae. Good luck PBart "Chechu" wrote: pbart, Thanks a lot for your help on this. I understand your suggestion, except when you say "will return a value from MATCH that provides a starting row/date for the subsequent Resource search". At some point I think that the formula I started to work uses some similar approach. This is how it looks like so far: INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE DATE"<="ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the resource""ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES ID"="Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective Date&ResID)",0)) The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet 2, sorted by Res ID, Eff Date, then with a serie of If I can determine the end date for the effective rate. I know, I know, this is confusing... but looks reasonable??? Still need to work on the N/A error handling, but that's not complex. THANKS! Cecilia On Mar 24, 7:10 pm, pbart wrote: Can you sort the second table starting with =TODAY() and running backwards? If so searching for the Activity Date within the Effective Date range will return a value from MATCH that provides a starting row/date for the subsequent Resource search. You could use CONCATENATE to produce a string representing the range to be searched, eg =CONCATENATE("B"&(16+F3)&":C21") (where F3 is the result of the first MATCH or the function itself). *Finally VLOOKUP, referencing this string using INDIRECT() and set to return the contents one column to the right of the ResID, would give the cost. "Chechu" wrote: I am needing some help on this situation. I have the data structured in this way: * *A * * * B * * * C * *Res ID *Cost * *Activity Date 1 *1234 * * $20 * *1/1/2010 2 *1234 * * $25 * *1/20/2010 3 *7432 * * $15 * *2/2/2010 4 *2574 * * $45 * *10/1/2009 5 *7432 * * $65 * *1/2/2010 It shows by resource (Col A), Hourly Cost (Col B), and Activity date (Col C). Then I have a second sheet, with this format: * *A * * * B * * * C * *Res ID *Cost * *Effective Date 1 *1234 * * $15 * *12/30/2009 2 *1234 * * $18 * *1/18/2010 3 *7432 * * $12 * *1/31/2010 4 *2574 * * $43 * *9/29/2009 5 *7432 * * $67 * *12/31/2009 Same format, but it shows the standard cost. Effective Date represents the day when this cost becomes available. What I am trying to do is in the first sheet, add a column with the current Cost (Sheet 2) at the moment of the transaction. Example: Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real cost coming from sheet 2 is $15 (because it is since 30/12/2009 to 1/18/2010, when a new cost becomes effective). Sheet 2 can contain two, three, four... x numbers of times the same resource (with different effective date, of course). Does somebody know how to calculate this??? In the meantime I am trying with Index, Match, Sumproduct, Lookup....... Thanks! Cecilia .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - Joel and PBart, I just can say 1.000.000 of THANKS for your help and guidance on this. I will try first with the Macro, since my Excel is crashing with all these formulas. Demo version is OK, but real data is too large for my humble Excel 2003... PBart, great suggestions, and I really appreciate it. I will follow your logic if I can’t make it with the Macro. Joel, your Macro is the kind of code that we love, short and clear!! I think that I can customize it based on the real file, I will start with it. THANKS!!! Cecilia |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup value, but between dates
On Mar 25, 10:03*pm, Chechu wrote:
On Mar 25, 9:54*am, pbart wrote: Chechu It looks like you now have alternative lines of investigation to follow up: the first using inbuilt functionality of the spreadsheet and the second introducing VBA macros. I do agree with Joel that there are dangers of hiding errors in complicated formulae. *If you adopt the formula approach, I would suggest you break the process into small steps using additional columns to hold intermediate results. For example, the first MATCH would tell you how many rows of the second sheet should be ignored because they postdate the activity. *That number can be checked for correctness. The CONCATENATE in the next column uses this value of 'number of rows to omit' in order to calculate the part of the range on sheet 2 that remains of interest (the starting cell will be further down the table than the original) and displays the result as a string. *Again this is something you can check. Finally a VLOOKUP in the next column can be applied to the range (the INDIRECT(cell) references the string from the previous column) and will find all information relating to the first instance of the ResID. * The remaining step before your spreadsheet hits an unsuspecting public is to 'hide your workings' by first hiding the extra columns and, possibly, deselecting headings from the view menu. I hope you are not offended by my suggesting how you might best arrange such a calculation but, like Joel, I do rather shy away from complex formulae. Good luck PBart "Chechu" wrote: pbart, Thanks a lot for your help on this. I understand your suggestion, except when you say "will return a value from MATCH that provides a starting row/date for the subsequent Resource search". At some point I think that the formula I started to work uses some similar approach. This is how it looks like so far: INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE DATE"<="ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the resource""ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES ID"="Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective Date&ResID)",0)) The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet 2, sorted by Res ID, Eff Date, then with a serie of If I can determine the end date for the effective rate. I know, I know, this is confusing... but looks reasonable??? Still need to work on the N/A error handling, but that's not complex. THANKS! Cecilia On Mar 24, 7:10 pm, pbart wrote: Can you sort the second table starting with =TODAY() and running backwards? If so searching for the Activity Date within the Effective Date range will return a value from MATCH that provides a starting row/date for the subsequent Resource search. You could use CONCATENATE to produce a string representing the range to be searched, eg =CONCATENATE("B"&(16+F3)&":C21") (where F3 is the result of the first MATCH or the function itself). *Finally VLOOKUP, referencing this string using INDIRECT() and set to return the contents one column to the right of the ResID, would give the cost. "Chechu" wrote: I am needing some help on this situation. I have the data structured in this way: * *A * * * B * * * C * *Res ID *Cost * *Activity Date 1 *1234 * * $20 * *1/1/2010 2 *1234 * * $25 * *1/20/2010 3 *7432 * * $15 * *2/2/2010 4 *2574 * * $45 * *10/1/2009 5 *7432 * * $65 * *1/2/2010 It shows by resource (Col A), Hourly Cost (Col B), and Activity date (Col C). Then I have a second sheet, with this format: * *A * * * B * * * C * *Res ID *Cost * *Effective Date 1 *1234 * * $15 * *12/30/2009 2 *1234 * * $18 * *1/18/2010 3 *7432 * * $12 * *1/31/2010 4 *2574 * * $43 * *9/29/2009 5 *7432 * * $67 * *12/31/2009 Same format, but it shows the standard cost. Effective Date represents the day when this cost becomes available. What I am trying to do is in the first sheet, add a column with the current Cost (Sheet 2) at the moment of the transaction. Example: Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real cost coming from sheet 2 is $15 (because it is since 30/12/2009 to 1/18/2010, when a new cost becomes effective). Sheet 2 can contain two, three, four... x numbers of times the same resource (with different effective date, of course). Does somebody know how to calculate this??? In the meantime I am trying with Index, Match, Sumproduct, Lookup....... Thanks! Cecilia .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - Joel and PBart, I just can say 1.000.000 of THANKS for your help and guidance on this. I will try first with the Macro, since my Excel is crashing with all these formulas. Demo version is OK, but real data is too large for my humble Excel 2003... PBart, great suggestions, and I really appreciate it. I will follow your logic if I can’t make it with the Macro. Joel, your Macro is the kind of code that we love, short and clear!! I think that I can customize it based on the real file, I will start with it. THANKS!!! Cecilia- Hide quoted text - - Show quoted text - Joel and PBart, I customized the Macro based on some data quality issues I have, and it works just PERFECT. Also I learned a lot which is fantastic. THANKS a lot for your help on this!!!!! Cecilia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using dates for lookup | Excel Worksheet Functions | |||
Lookup between two dates | Excel Worksheet Functions | |||
Lookup dates | Excel Programming | |||
Lookup between dates | Excel Discussion (Misc queries) | |||
LOOKUP BETWEEN 2 DATES | Excel Programming |