Assistance required

peterbata

New Member
Joined
Feb 20, 2016
Messages
20
Hello all. I don't have very much experience with Excel but I am really trying to do my best. I am working on a small project and thought that Excel would fit the bill. I have run into a little snag (brain freeze). Here are some details.

A friend, who has a small courier company, has asked me to help out with putting together a customer price list. The list is made up of approx 100 postal codes. Using an Excel add-n (CDXZipStream) I'll be able to fetch the distance between all 100 postal codes. That works out to 10,000 cells. I'll call the worksheet that holds the fetched distances "calc". The process of going out to Bing for all the distances is excruciating long and can only be done if the add-in is installed locally. I would only want to do this once. Distributing the excel file would be difficult as the machine accessing the file would require the add-in. Not possible, and probably not necessary.

So, what I did was to convert the fecthed distances into whole numbers via roundup. So, 1.29 km becomes 2.0 km This information resides on a worksheet named "value". On another worksheet "rates" I created what I guess can be called a multiplier grid. Which looks something like this:

So any value that falls in between let's say 1 and 5 would use row 1 and it's corresponding rates.

DIST842Overnight
16.009.2513.7526.25
57.0010.25etcetc
108.0011.25etcetc
1510.0012.25etcetc

<tbody>
</tbody>

The actual price list which will be either printed or emailed to a client I named "client" This sheet has all 100 postal codes running down the first column titled "zone" To the right are columns with headings for delivery preference 8 hours, 4h, 2h and overnight.

Now, here is where I am stuck. If a client from postal H2L requests a price list, then obviously he would like to know the cost of shipping his parcel to any of the remaining 99 postal codes. I created a dropdown at the very top of the sheet which would allow the courier company to select the desired postal code. I can picture what should happen in my head but can't seem to translate that onto my worksheet(s).

1) Select postal code from dropdown
2) The "value" sheet is scanned for all possible combinations H2L -> XXX
3) All distances are calculated against the "rates" table
4) Price list shows all the possible dollar combinations.

I would have provided the worksheet but I don't think that there is a way for me to attach the file. Perhaps I can make it accessible offsite. By the way, what I am working on now only contains a limited combination of postal codes. 14RX14C

Any help and/or suggestions would be appreciated immensely. Thank you. Peter
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You could use IF(AND statements.

IF(AND(Distance>1,<5,8,), Price $,IF(AND(Distance>5,<10,4,), Price $

The problem you'll run into is the multitude of combinations. 14x14 would be 196 combos which you wouldn't be able to nest that many formula.

You might be better off doing this by flat rate by size of box. 14.95 in a flat rate box by Postal Office should ship anywhere in the US, that may not be very exciting for your close customers, but now days it seems anything is $10 to ship, unless your amazon.
 
Upvote 0
Thank you so much for the prompt reply. I really appreciate it. Actually, once the entire "value" table is populated with the Rounded Up info that would be more like 100X100. IF my math is correct that would be 10,000 combos. Could that be? I don't if this is possible. Couldn't I use an Index / Match and Vlookup formula?

1) find the intersecting data
2) crosscheck against the rates table
3) yield info into the price table.

https://mega.nz/#!IxIzXQgY!4jqQAuUFOu_WtV-a5XlGTIgJqHX5iSqqw2Hl4STEJnA
 
Last edited:
Upvote 0
Actually, this courier does not charge by weight or size. It's the distance and the delivery service that the client chooses that really matter.

Hence, the 8,4,2, or overnight delivery. Different rate for each. I am still trying to apply your formula to my worksheet. Having a little trouble figuring it out. But, I'll keep trying.

You could use IF(AND statements.

IF(AND(Distance>1,<5,8,), Price $,IF(AND(Distance>5,<10,4,), Price $

The problem you'll run into is the multitude of combinations. 14x14 would be 196 combos which you wouldn't be able to nest that many formula.

You might be better off doing this by flat rate by size of box. 14.95 in a flat rate box by Postal Office should ship anywhere in the US, that may not be very exciting for your close customers, but now days it seems anything is $10 to ship, unless your amazon.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top