dan_m1les
Member
Registered: 8th May 06
Location: Burnham, Buckinghamshire
User status: Offline
|
Since the new year, at work we have had to chage courrier companies from 1 company to 3 different companies for sending our parcels, as they all offer pro's and con's with price either to do with weight, number of boxes or delivery location.
So we now have 5 pages of inforamtion to try to work out which courrier will be the cheapest for us and the customer, and its providing several headaches.
Is there a way to set up a spread sheet and entre in the number of boxes, weight and delivery post code and it will tell you the price and which courrier to use?
e.g. 3 boxes with a total weight of 22kgs going to G12 = £9.50 via UPS
or 1 box to with a total weight of 9kgs going to BT44 = £13.50 via Parcel Force
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
What's the postcode used for? Do you need to calculate distances?
But yes, potentially you could create a spreadsheet to do that. However if you're having to deal with PAF (postcodes) data and needing to calculate distances between postcodes/addresses, then i wouldn't recommend using Excel.
[Edited on 08-01-2014 by Dom]
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
You want a database to do this for you.
It can be done in excel but would be very clunky.
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
The other thing is that Excel won't know the Pro's and con's of the company and which senario you would use X over Y when they are the same price.
[Edited on 08-01-2014 by Gaz]
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Gaz
You want a database to do this for you.
You use a DB for large amounts of data. The application dan_m1les is suggestion is mostly logic based, so yes it could be done in Excel.
Only issue i see is if you have to deal with PAF data, calculating distances. In which case Excel isn't going to handle millions of rows of postcode data and if you're having to go to the lengths of using a DB for the PAF data or using external data via API's, then you might as well create the app in something that's a bit more suited to the job.
quote:
Originally posted by Gaz
The other thing is that Excel won't know the Pro's and con's of the company and which senario you would use X over Y when they are the same price.
You'd add weighting to results based on X,Y,Z logic; isn't particularly difficult.
|
dan_m1les
Member
Registered: 8th May 06
Location: Burnham, Buckinghamshire
User status: Offline
|
The distance isn't a factor, the courrier companies charge more to say northern ireland or the schottish highlands etc.
We have the date base that shows what is an "extra charge post code" and such.
I think it could be done by saying 'if' the weight is this and 'if' the post code is this and 'if' it is this many boxes it equals this.
No courrier service overlaps there is alwasys a definatvie aswer as such.
If a custmer in glasgow orders 1 box of equipment at 10kgs it might go say via UPS, but if the same customer in glasgow order 2 boxes of equipment at 20kgs it might go via parcel force for example.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
That does sound doable in Excel.
PAF size is a red herring. Even if you wanted to do the pythag in Excel, you still could, just with lower res data.
The outward (left part) set is <3000 rows. If you don't care about number its only 121.
Your allow list is probably less again.
Need to know what goes in to the number of boxes calc. Not just that more might means someone else, how do you finally decide? On price? If so you also need their charts.
Paste it all in here.
[Edited on 08-01-2014 by Ian]
|
dan_m1les
Member
Registered: 8th May 06
Location: Burnham, Buckinghamshire
User status: Offline
|
I can email the excel document to you tomorrow? If that'll work?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by Ian
That does sound doable in Excel.
PAF size is a red herring. Even if you wanted to do the pythag in Excel, you still could, just with lower res data.
The outward (left part) set is <3000 rows. If you don't care about number its only 121.
You couldn't use the postal area on it's own (some areas are far too large), so you'd need the district as well but you could still be 10/20+ miles out of where it's actually going. Guess it depends on how accurate you need the result to be.
Although it doesn't sound like Dan needs to do any distance calculations....
Dan - IF statement logic is probably the simplest option although i'd personally look at doing it in VBA as you could increase the calculation/Excel performance and it'd be easier to debug rather than as a formula.
Either way, plenty of people on here that can give you a hand if you get stuck 
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Happy to have a look dan 
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Sounds like a piece of piss to me.
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
I made this for someone on here a while back:
https://www.youtube.com/watch?v=tH7hjPgsjuY
Their requirement was to work out round trip times and distances for two or more postcodes to see if a delivery driver could do it in a day.
Your requirement is less complicated by far.
[Edited on 08-01-2014 by evilrob]
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
quote:
Originally posted by Dom
i'd personally look at doing it in VBA as you could increase the calculation/Excel performance
Don't get me wrong, I'm a complete bastard for a bit of VBA - but as a rule, native Excel built-in commands are almost always faster than VBA UDFs.
|
dan_m1les
Member
Registered: 8th May 06
Location: Burnham, Buckinghamshire
User status: Offline
|
If anyone could u2u me their email address I'll email it over in the morning
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
U2ud
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by evilrob
quote:
Originally posted by Dom
i'd personally look at doing it in VBA as you could increase the calculation/Excel performance
Don't get me wrong, I'm a complete bastard for a bit of VBA - but as a rule, native Excel built-in commands are almost always faster than VBA UDFs.
Really? I'm surprised. Saying that, Excel isn't my go-to apart from doing quick equations or knocking up pretty graphs
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
quote:
Originally posted by Dom
quote:
Originally posted by evilrob
quote:
Originally posted by Dom
i'd personally look at doing it in VBA as you could increase the calculation/Excel performance
Don't get me wrong, I'm a complete bastard for a bit of VBA - but as a rule, native Excel built-in commands are almost always faster than VBA UDFs.
Really? I'm surprised. Saying that, Excel isn't my go-to apart from doing quick equations or knocking up pretty graphs
Yep. Even if you do things properly with For/Each loops through objects rather than For/Next loops. You can use worksheet functions in your VBA, though -
Application.WorksheetFunction.<insert function of your choice here>
e.g.:
code:
Sub UsingAWorkSheetFunction()
' /* Note: Only use Application.WorksheetFunction.Min or .Max for ranges as there is some overhead.
' Don't use it to compare two values. e.g. Application.WorksheetFunction.Max(Value1, Value2)
' To compare just two values, "If Value1 > Value2" is about 10x faster. */
Dim rngRange As Range
Dim varAnswer as Variant
Set rngRange = Worksheets("DomsAwesomeWorksheet").Range("A1 100")
varAnswer = Application.WorksheetFunction.Min(rngRange)
MsgBox varAnswer
End Sub
More hints for Excel VBA optimisation here:
http://www.cpearson.com/excel/optimize.htm
[Edited on 09-01-2014 by evilrob]
|
