Putting shipping rates in rows (reading from table)

Posted on 2012-09-10
Last Modified: 2012-09-15

I have two sheets, in one sheet is my "products" where I want to have shipping rates calculated from other sheet i.e. "Shipping rates" but displaying in rows against each product item.

Example (product sheet where "Tariff" being calculated from "Shipping rates":

Product          Weight      Tariff
product A          0.6      us=10,gb=8,es=8
product B          1.6      us=14,gb=12,es=12
product C           2      us=14,gb=12,es=10

While my "Shipping rates" contain following tariff table for each country:

wt_start       wt_end       Rate       Country
0       0.5       8       us
0.5       1       10       us
1       1.5       12       us
1.5       2       14       us
2       2.5       16       us
2.5       3       18       us
0       1       8       gb
1       1.5       10       gb
1.5      2      12      gb
2      2.5      13      gb
0      2.5      10      es
2.5      4.5      12      es
4.5      2      14      es

I need a macro so that "Tariff" column is calculated automtically based on Shipping rates for each product in product sheet.

I am attaching the file and appreciate if you can write code in it.

Many thanks.
Question by:NickHoward
    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    Does this have to be VBA? You can even use this formula in D2 and copy it down

    ="us="&INDEX('Shipping rates'!$C:$C,MATCH("us",'Shipping rates'!D:D,0)-1+MATCH(B2-0.001,OFFSET('Shipping rates'!$D$1,MATCH("us",'Shipping rates'!D:D,0)-1,-3,COUNTIF('Shipping rates'!D:D,"us"))))&",gb="&INDEX('Shipping rates'!$C:$C,MATCH("gb",'Shipping rates'!D:D,0)-1+MATCH(B2-0.001,OFFSET('Shipping rates'!$D$1,MATCH("gb",'Shipping rates'!D:D,0)-1,-3,COUNTIF('Shipping rates'!D:D,"gb"))))&",es="&INDEX('Shipping rates'!$C:$C,MATCH("es",'Shipping rates'!D:D,0)-1+MATCH(B2-0.001,OFFSET('Shipping rates'!$D$1,MATCH("es",'Shipping rates'!D:D,0)-1,-3,COUNTIF('Shipping rates'!D:D,"es"))))
    LVL 25

    Expert Comment

    If you change the format of the shipping rates table ... a vlookup will work very easily.

    Author Comment

    Thanks for the solution.

    I wish to have a macro as I have huge shipping rate table for 24 countries being loaded from database.

    Hope someone can help in this direction.

    I will share the points indeed.

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    If the first solution gives correct results then I can put it into a macro.
    LVL 10

    Expert Comment

    Is there a reason to have the Tariff information be in a single cell per product? Once you assemble that information for 24 countries, that cell will be very long and practically unusable by a human. Would it work to have 24 tariff columns (one per country) instead?

    Note: It is relatively straightforward to write a macro to populate the information whether the desired result is one cell per product or multiple tariff cells.

    Author Comment

    You are correct - infact I am making a product feed in xml where tariff has to be on single line. They do support 255 characters though.

    Only issue to be resolved when creating tariff is that if no tariff found then it should not include, for instance:


    should apear as

    Macro loop through all ISO country abbreviations and create the tariff accordingly.

    LVL 10

    Expert Comment

    I have written a macro (below) that should do what you're asking, but I get different values for "es" than you suggested:
    Sub ComputeTariffs()
    Dim i As Long
    Dim j As Long
    Dim lastShipping As Long
    Dim oProdSheet As Worksheet
    Dim oShipSheet As Worksheet
    Dim tariff As String
        Set oProdSheet = Sheets("product")
        Set oShipSheet = Sheets("Shipping rates")
        lastShipping = oShipSheet.Range("A" & oShipSheet.Rows.Count).End(xlUp).Row
        For i = 2 To oProdSheet.Range("A" & oProdSheet.Rows.Count).End(xlUp).Row
            tariff = ""
            For j = 2 To lastShipping
                If oShipSheet.Cells(j, 1).Value < oProdSheet.Cells(i, 2).Value And _
                    oShipSheet.Cells(j, 2).Value >= oProdSheet.Cells(i, 2).Value Then
                    If tariff <> "" Then tariff = tariff & ","
                    tariff = tariff & oShipSheet.Cells(j, 4).Value & "=" & _
                        oShipSheet.Cells(j, 3).Value
                End If
            Next j
            If tariff <> "" Then
                oProdSheet.Cells(i, 4) = tariff
            End If
        Next i
    End Sub

    Open in new window

    LVL 10

    Expert Comment

    One thing you didn't specify is what to do when the weight is matches multiple rows. Is a weight of 0.5 supposed to yield us=8 or us=10? The solution posted above assumes that us=8 is the correct value.

    wt_start       wt_end       Rate       Country
    0       0.5       8       us
    0.5       1       10       us

    Author Comment

    Hi tdlewis,

    It okay to have match lowest value like us=8 you suggested above.

    I tried the macro but getting erors. Can you please check again? May be putting back in my earlier excel can help to have placed in correct order?

    LVL 10

    Accepted Solution

    I just opened your original worksheet, pasted the code, and it runs without any errors. I've attached the updated Excel file.

    When I was testing the macro, I put the result into column D so that I could see the macro results alongside your example. In order for the results to appear in column C this line:
        oProdSheet.Cells(i, 4) = tariff
    will need to change to:
        oProdSheet.Cells(i, 3) = tariff

    However, that does not explain why you're getting an error. What is the error message and on which line does it occur?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now