Pull text field with combination of text and date field

Question

QUESTION: My Master table shows effective Duty Rates of materials. Hence Transaction Table searches Master table with combination of Prod and ValidFrom.

Master Table Prod Valid From Duty Rate 000 00/01/00 0.00% DTY 01/01/06 4.20% DTY 28/06/07 4.25% IWRR 24/10/07 1.50% IWRR 01/01/09 4.00% IRC 28/02/09 1.50% IRC 28/02/10 2.00%

Transaction Data Prod Shp Dt Duty Rate DTY 24/08/06 4.20% DTY 24/08/09 0.00% Effective duty is 4.25%

Formula written on C13 is SUMPRODUCT(--(Prod=A13),--(Valdt=VLOOKUP(B13,Valdt,1)),(DutyRt)) Now in C14 Results are not correct as Vlookup is giving wrong results. Since Date can be any date so Vlookup is inserted. But it should work in combination of Product and Date

Attached Screenshot offile for your reference. I cannot use Array formula as it takes huge space. My next step is to convert the equation into VBA.

Please help

ANSWER: Grisha,

if you duty data is sorted by product as you show in your top table, then this worked for me:

=SUMPRODUCT(--(Prod=A14),--(valdt=VLOOKUP(B14,OFFSET(valdt,MATCH(A14,Prod,0)-1,0,COUNTIF(Prod,A14),1),1,TRUE)),(DutyRt))

---------- FOLLOW-UP ----------

QUESTION: Thanks a lot, It Worked. Only problem is if I put any date like 01/12/05 then formula returns #N/A. Where as it should return 0. Hope this can be controlled thru ISNA function. I need to copy this formula in around 20000 rows. My others formulas in the worksheets is also long hence PC is getting slow.

How to take this formula in VBA function. Is it possible for VBA to recalculate the function on corresponding field change event like Application.Volatile = False

Answer

Grisha,

=if(iserror(VLOOKUP(B14,OFFSET(valdt,MATCH(A14,Prod,0)-1,0,COUNTIF(Prod,A14),1),1,TRUE))),0,SUMPRODUCT(--(Prod=A14),--(valdt=VLOOKUP(B14,OFFSET(valdt,MATCH(A14,Prod,0)-1,0,COUNTIF(Prod,A14),1),1,TRUE)),(DutyRt)))

Here is a VBA function.

you must put it in a general module - not a sheet module or the thisworkbook module

usage:

C14: =CalcRate(A14,B14,Prod,Valdt,DutyRt)

You might want to remove the Application.Volatile statement from the function. It should update because all the dependent cells are in the arguments list. So it should calculate appropriately without the volatile statement.

Public Function CalcRate(rProd As Range, rDate As Range, rProdList As Range, rValdt As Range, rDutyRt As Range) ' Application.Volatile Dim prd As String, vProd As Variant, vDate As Variant, vRate As Variant Dim dt As Date, i As Long, lStrt As Long, lEnd As Long, res As Variant Dim cnt As Long, j As Long If rProd.Count > 1 Or rDate.Count > 1 Or rProdList.Columns.Count > 1 Or _

  rValdt.Columns.Count > 1 Or rDutyRt.Columns.Count > 1 Or rProdList.Areas.Count > 1 Or _
  rValdt.Areas.Count > 1 Or rDutyRt.Areas.Count > 1 Then
 CalcRate = CVErr(xlErrRef)
 Exit Function

End If If Not IsDate(rDate) Then

 CalcRate = CVErr(xlErrValue)
 Exit Function

End If

prd = LCase(rProd.Value) dt = rDate.Value vProd = rProdList.Value vDate = rValdt.Value vRate = rDutyRt.Value cnt = Application.CountIf(rProdList, rProd)

ReDim vv(0 To cnt, 0 To 1) vv(0, 0) = 0 vv(0, 0) = 0

For i = 1 To UBound(vProd, 1)

 If LCase(vProd(i, 1)) = prd Then
   If lStrt = 0 Then
      lStrt = i
   ElseIf lStrt > 0 Then
      lEnd = i
   End If
 ElseIf lEnd > 0 Then
   Exit For
 End If

Next

j = 0

For i = lStrt To lEnd

 j = j + 1
 vv(j, 0) = CDbl(vDate(i, 1))
 vv(j, 1) = vRate(i, 1)

Next

res = Application.VLookup(CDbl(dt), vv, 2, True)

If IsError(res) Then

  CalcRate = 0

Else

  CalcRate = res

End If End Function

Lightly tested, but it worked fine for me.

all that said, since my focus was on getting your function to work, I ignored the fact that my solution could be used stand alone and is not an array formula (sumproduct, even though it doesn't have to be array entered still represents an array formula with its attendant slowness - and a VBA solution isn't going to be faster I wouldn't think).

VLOOKUP(B14,OFFSET(valdt,MATCH(A14,Prod,0)-1,0,COUNTIF(Prod,A14),1),1,TRUE))

returns the date you want. However, if the range was expanded to two columns, it could return the rate as well and then the sumproduct is not needed.

=VLOOKUP(B14,OFFSET(valdt,MATCH(A14,Prod,0)-1,0,COUNTIF(Prod,A14),2),2,TRUE)

if you want to guard against the error you spoke of in your note:

=IF(ISERROR(VLOOKUP(B14,OFFSET(valdt,MATCH(A14,Prod,0)-1,0,COUNTIF(Prod,A14),2),2,TRUE)),0,VLOOKUP(B14,OFFSET(valdt,MATCH(A14,Prod,0)-1,0,COUNTIF(Prod,A14),2),2,TRUE))

Question

QUESTION: Hi Bob My Master table shows effective Duty Rates of materials. Hence Transaction Table searches Master table with combination of Prod and ValidFrom.

Master Table Prod Valid From Duty Rate 000 00/01/00 0.00% DTY 01/01/06 4.20% DTY 28/06/07 4.25% IWRR 24/10/07 1.50% IWRR 01/01/09 4.00% IRC 28/02/09 1.50% IRC 28/02/10 2.00%

Transaction Data Prod Shp Dt Duty Rate DTY 24/08/06 4.20% DTY 24/08/09 0.00% Effective duty is 4.25%

Formula written on C13 is SUMPRODUCT(--(Prod=A13),--(Valdt=VLOOKUP(B13,Valdt,1)),(DutyRt)) Now in C14 Results are not correct as Vlookup is giving wrong results. Since Date can be any date so Vlookup is inserted. But it should work in combination of Product and Date

Attached Screenshot offile for your reference. I cannot use Array formula as it takes huge space. My next step is to convert the equation into VBA.

Please help

ANSWER: SUMPRODUCT(--(Prod=A13),--(Valdt=B13),(DutyRt))

---------- FOLLOW-UP ----------

QUESTION: Dear BOB

if you could give me your email address then i could send you myfile.

Answer

If B13 is not in Valdt, then this part of your formula will never be found: --(Valdt=VLOOKUP(B13,Valdt,1)) because your Vlookup is looking up a value (B13) in Valdt, so I would think it should be there.

send a sample file to me at bobumlas@yahoo.com, use subject of "AllExpertsQ" and repeat what the issue is.

Question

Hi Aidan My Master table shows effective Duty Rates of materials. Hence Transaction Table searches Master table with combination of Prod and ValidFrom.

Master Table Prod Valid From Duty Rate 000 00/01/00 0.00% DTY 01/01/06 4.20% DTY 28/06/07 4.25% IWRR 24/10/07 1.50% IWRR 01/01/09 4.00% IRC 28/02/09 1.50% IRC 28/02/10 2.00%

Transaction Data Prod Shp Dt Duty Rate DTY 24/08/06 4.20% DTY 24/08/09 0.00% Effective duty is 4.25%

Formula written on C13 is SUMPRODUCT(--(Prod=A13),--(Valdt=VLOOKUP(B13,Valdt,1)),(DutyRt)) Now in C14 Results are not correct as Vlookup is giving wrong results. Since Date can be any date so Vlookup is inserted. But it should work in combination of Product and Date

Attached Screenshot offile for your reference. I cannot use Array formula as it takes huge space. My next step is to convert the equation into VBA.

Please help

Answer

Not sure why the sumprodut is using a vlookup ¨C IF I follow the question correctly you want to return the value where the PROD item and the DATE are the same? If so

=sumproduct(--(ProdRange=A13),--(ValdateRange=Valdate),DutyRateRange)

should do what you want

NOTE that the information on the screen shot was difficult for me to read (probably my eyesight!) so if you want to mail me any files you can do so at aidan.heritage@virgin.net