Lookup value, but between dates

[Chechu]

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

 

[joel]

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 dat
which is closest to the activity date since the price may change
couple of times. I cn easily do this with a macro but it appears yo
are looking for a formula solution?

 

[pbart]

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]

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/showthread.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

 

[Chechu]

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

 

[joel]

This is the macro solution. My preference is to always use macr
instead of a complicated formula. Macros are easier to debug and can b
documented. Complicated formulas cannot be documented. Macros als
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

 

[pbart]

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]

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 breakthe
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 remainsof
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 isto
'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










- 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

 

[Chechu]

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