Pull text field with combination of text and date field

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:51 PM GMT

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.

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

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.

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

QUESTION: Dear BOB

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.