Looking up transaction table against pricelist with quantity breaks

[daftcam]

Hi all, first time poster so hoping someone can assist!

I have two tables: a table of transactions and a pricing table for both June and September. We're trying to establish whether the price since June has increased enough to cover additional costs (from Brexit price rises).


So if Customer A was paying £10.00/kg for a product back in June, the June pricebook will say £10.00/kg. But then the account managers were under instructions to increase prices by 7% across the board, so the September price should be £10.70/kg. If the price hasn't increased by 7%, we need to calculate how much we've lost as a result of that.


Normally that would be fairly easy, but the problem is that each customer / product combination could have a pricebreak for different quantity sold. So for that example above, maybe if they purchase 25kg they get £10.00/kg, but if they purchase 25kg-1000kg it's £8.00/kg, 1000kg-3000kg it's £6.00/kg and so on. And the pricebook for June and September have the price breaks running vertically, so if I do a VLOOKUP finding a customer / product combination, it finds the first entry and returns that value (£10.00/kg) but it could be that that customer purchased 2000kg and it should be returning the £6.00/kg price.


Basically I need the formula to be able to VLOOKUP the customer / product combination (column A in "Invoice Data"), and then look in the quantity column (column I in "Invoice Data") and find what the quantity break is, and only then return the price quoted (from column H in "June" and "September"). The results will populate into column K and L on "Invoice Data" and I'll reconcile the difference from there. It will need to be able to look if the quantity is between each figure, as it may not be an exact match of quantity.


As an example, on invoice 504564, Customer A purchased 100kg of Product A. Pricing in June was £21.91/kg, pricing in September is £21.91/kg (but should have been £23.44/kg). Based on that, we've lost £1.53/kg or £153.00 on that transaction. But the formula has to be able to look at the different pricing by quantity (so if they'd ordered 400kg, it would have given a different price for both months).


I've spoken to people more knowledgeable than I and they've mentioned I might need an array formula which I'm unfamiliar with. Right now I have a mammoth formula (below) which is working for the majority of the data, but if the quantity sold is larger than the largest price break, it brings up results from other customer / product combinations.

Current Formula:
=IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0))),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+1)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+1),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+2)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+2),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+3)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+3),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+4)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+4),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+5)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+5),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+6)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+6),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+7)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+7),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+8)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+8),
IF(I2<=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+9)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+9),
IF(I2>=(INDEX(June!$F$2:$F$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+10)),INDEX(June!$H$2:$H$135,MATCH('Invoice Data'!A2,June!$A$2:$A$135,0)+10))))))))))))

Hopefully this makes sense, but if you need any further information please let me know!

 

[Veritan]

Hi daftcam, welcome to MrExcel. In my opinion (and anybody who knows more about this than I do, feel free to disagree), you can't get what you need with a formula, certainly not easily. However, I do think I was able to come up with a VBA solution for you that should work. This solution is dependent on the data in your Combined column in both your June and September sheets being completely grouped together. In other words, all occurrences of 448158194586ORR are next to each other. The order of the groups isn't important, the Combined values starting with 2 can come before or after the Combined values starting with 4. However, all occurrences of each individual Combined value need to be next to each other. If I'm understanding you correctly, this is already the case with your data. If it's not, you may want to consider grouping them like that.

Take this code and insert it into a module in your workbook, then run the Sub PriceData. That should get you what you need. If you're not sure how to do that, a quick search for how to access VBA and insert modules will get you what you need.

Option Explicit
Sub PriceData()
    Application.ScreenUpdating = False
    Sheets("Invoice Data").Activate
    Call Pricing("June", -2, -10, Range("J1").End(xlDown).Row, 11)
    Call Pricing("September", -3, -11, Range("J1").End(xlDown).Row, 12)
End Sub
Private Sub Pricing(SheetName$, QOff%, COff%, RowNum%, ColNum%)
    Dim Price() As Variant, r As Range, WriteRange As Range, CombinedFirstRow, CombinedLastRow
    Dim PriceRow%, i%, QuantityOrdered%, Combined$
    ReDim Price(1 To RowNum - 1)
    For Each r In Range(Cells(2, ColNum), Cells(RowNum, ColNum))
        QuantityOrdered = r.Offset(, QOff)
        Combined = r.Offset(, COff)
        Sheets(SheetName).Activate
        Set CombinedFirstRow = Range("A1:A" & Range("A1").End(xlDown).Row).Find(What:=Combined, LookAt:=xlWhole, SearchDirection:=xlNext)
        If CombinedFirstRow Is Nothing Then GoTo Skip
        CombinedFirstRow = CombinedFirstRow.Row
        Set CombinedLastRow = Range("A1:A" & Range("A1").End(xlDown).Row + 1).Find(What:=Combined, LookAt:=xlWhole, SearchDirection:=xlPrevious)
        If CombinedLastRow Is Nothing Then GoTo Skip
        CombinedLastRow = CombinedLastRow.Row
        If QuantityOrdered < Range("F" & CombinedFirstRow) Then
            PriceRow = CombinedFirstRow
        Else
            PriceRow = CombinedFirstRow + WorksheetFunction.Match(QuantityOrdered, Range("F" & CombinedFirstRow, "F" & CombinedLastRow), 1) - 1
        End If
        r = Range("H" & PriceRow)
Skip:
        If r = "" Then r = "Value not found"
        i = i + 1
        Price(i) = r
    Next r
    Sheets("Invoice Data").Activate
    Set WriteRange = Range(Cells(2, ColNum), Cells(RowNum, ColNum))
    WriteRange = Application.Transpose(Price)
    Erase Price
End Sub

Let me know if this works for you, good luck!

 

[daftcam]

Hi Veritan, thanks very much for the quick response!

I've got a (very) basic understanding of macros (mostly recording basic functions), so I managed to insert your code into a module. But unfortunately when I go to run the macro, it comes up with a Run-time error '13': Type mismatch. When I go to debug, it highlights the "QuantityOrdered = r.Offset(, QOff)" part of the code.

I've made sure that on both the June & September pricelists, all of the combined codes are grouped together. I didn't know if it would make a difference, but I also did this on the invoices sheet as well.

Is there anything else you can think of that could be causing this?

Thanks again for your help, really appreciate it!

 

[Veritan]

You should be able to keep the combined values on your invoice sheet in whatever order you like, that shouldn't make a difference. As far as the Type Mismatch, my guess is that there's something in the Invoiced Qty column on your invoice sheet that isn't compatible with the current declaration of QuantityOrdered as an Integer. Probably the easiest way to fix this is to simply remove the "%" sign after the QuantityOrdered variable 3 lines above the yellow highlighted line. Try that and see if it works. You can also take a look at this link and see if that helps any. This is the Microsoft help page for a Type mismatch error.

https://msdn.microsoft.com/en-us/library/aa264979(v=vs.60).aspx

 

[daftcam]

Hi Veritan,

Thanks for the reply, I believe that the reason it wasn't working was I had a combined cell in A1 for both pricebooks with the title "June" and "September" which was interferring with the macro. Have removed those headings and seems to be working perfectly with the sample data I provided.

Now I'll just adapt it to my main sheet and should work great

Thanks again for your help, first time I've ever used one of these forums to troubleshoot and it was a painless experience!

PS. You also managed to impress our IT guy with how you defined QOff% and COff%, plus you've left it open for us to change the variables in future if we need to, so thanks very much!

 

[Veritan]

Oh yeah, the names in A1 will definitely interfere with the code. I had put your example headers in row 1, so that's definitely what was causing the issue. You can adjust the code to suit so you can put your titles back in by making A1 in the two "Set..." lines A2, I believe that should fix it. I'm happy to hear this was helpful for you, and that the code will work in the future for you. Thanks for trying out this forum, feel free to come back with any other questions you have. Best of luck!

 

[Veritan]

I went ahead and tested out the code after putting in your titles and merging the cells. Also did some basic code cleaning to make it faster. Try this code out below, it accounts for having the titles in all 3 sheets.

Option Explicit
Sub PriceData()
    Application.ScreenUpdating = False
    Sheets("Invoice Data").Activate
    Call Pricing("June", -2, -10, Range("J2").End(xlDown).Row, 11)
    Call Pricing("September", -3, -11, Range("J2").End(xlDown).Row, 12)
End Sub
Private Sub Pricing(SheetName$, QOff%, COff%, RowNum%, ColNum%)
    Dim Price() As Variant, r As Range, WriteRange As Range, CombinedFirstRow, CombinedLastRow
    Dim PriceRow%, i%, QuantityOrdered%, Combined$
    ReDim Price(1 To RowNum - 1)
    For Each r In Range(Cells(3, ColNum), Cells(RowNum, ColNum))
        QuantityOrdered = r.Offset(, QOff)
        Combined = r.Offset(, COff)
        With Sheets(SheetName)
            Set CombinedFirstRow = .Range("A2:A" & .Range("A2").End(xlDown).Row).Find(What:=Combined, LookAt:=xlWhole, SearchDirection:=xlNext)
            If CombinedFirstRow Is Nothing Then GoTo Skip
            CombinedFirstRow = CombinedFirstRow.Row
            Set CombinedLastRow = .Range("A2:A" & .Range("A2").End(xlDown).Row + 1).Find(What:=Combined, LookAt:=xlWhole, SearchDirection:=xlPrevious)
            If CombinedLastRow Is Nothing Then GoTo Skip
            CombinedLastRow = CombinedLastRow.Row
            If QuantityOrdered < .Range("F" & CombinedFirstRow) Then
                PriceRow = CombinedFirstRow
            Else
                PriceRow = CombinedFirstRow + WorksheetFunction.Match(QuantityOrdered, .Range("F" & CombinedFirstRow, "F" & CombinedLastRow), 1) - 1
            End If
            r = .Range("H" & PriceRow)
        End With
Skip:
        If r = "" Then r = "Value not found"
        i = i + 1
        Price(i) = r
    Next r
    Set WriteRange = Range(Cells(3, ColNum), Cells(RowNum, ColNum))
    WriteRange = Application.Transpose(Price)
End Sub

 

[daftcam]

Hi Veritan,

Thanks for that, I've copied over the cleaner coding, but the merged cells I kept in last time we're purely to demonstrate what the sheet names were in my example. For practical purposes, the sheets don't have any combined cells in them.

Thanks again for your help!