trickery.net  

Go Back   trickery.net > General > Discussion

Reply
 
Thread Tools
Old 05-Dec-2011, 19:13   #61
Bilb
Amazeballs!
Bilb's Avatar
Join Date: Oct 2003
Location: Elite Elevated Equine Division
Posts: 11,283
Bilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond repute
If QT's avatar has any more snow, it will be jumping, I mean "lowering" itself out of the nearest window.
__________________
@TomJJarvis
Bilb is offline  
Send a message via Skype™ to Bilb  jarvthelegend 
Reply With Quote
Old 06-Dec-2011, 15:01   #62
[n00b]Zippy
Waterborne Trailer-Trash
[n00b]Zippy's Avatar
Join Date: Oct 2003
Location: Shropshire Union Canal
Posts: 13,057
[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute
If I add a Checkbox (a standard Excel one, not an ActiveX one) to a spreadsheet, how do I refer to it in code?
__________________
And now we rise and we are everywhere
[n00b]Zippy is online now  
Send a message via MSN to [n00b]Zippy
Reply With Quote
Old 11-Jan-2012, 11:38   #63
qui
Iron Man 3
qui's Avatar
Join Date: Oct 2003
Location: Uk
Posts: 4,128
qui has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond repute
Call it Dave.
__________________
i-rox - rtfm - yoga - dvorak - qupp - timmeh - japan & nz - marik
qui is offline  
 digitalKwee 
Reply With Quote
Old 11-Sep-2012, 15:03   #64
MZhukov
I liek boobies
MZhukov's Avatar
Join Date: Oct 2003
Location: Essex, England
Tournaments Won: 1
Posts: 1,735
MZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond repute
Excel woes. I have a head ache trying to work this out and require some trickery assistance.

I have two date/time fields say;

01/02/12 12:00am and 01/02/12 13:34pm

I need to work out, over these two fields whether there is a four hour (or more) period between 00:00 and 08:00 and bring back a postive response. So in this example it would be where as;

01/02/12 06:30am and 01/02/12 11:30am doesn't

Can any one assist?
__________________
24 hours in a day, 24 beers in a case......
MZhukov is offline  
 Mzhukov123  Tetris Champion
Reply With Quote
Old 11-Sep-2012, 15:09   #65
Q-Target
AKA QuebecTango
Q-Target's Avatar
Join Date: Oct 2003
Location: Not Brighton
Posts: 36,945
Q-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond repute
So you want a TRUE/FALSE result where the number of hours in a given period that occur within the 00:00-08:00 range is greater than or equal to 4.

It sounds like it would be simple but excel and time calculations = fucking horrible.
Q-Target is online now  
Reply With Quote
Old 11-Sep-2012, 15:11   #66
MZhukov
I liek boobies
MZhukov's Avatar
Join Date: Oct 2003
Location: Essex, England
Tournaments Won: 1
Posts: 1,735
MZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond repute
Yep a simple true false is fine and it is horrid. I want to jump from the 19th floor :/
__________________
24 hours in a day, 24 beers in a case......
MZhukov is offline  
 Mzhukov123  Tetris Champion
Reply With Quote
Old 11-Sep-2012, 15:15   #67
Harry Palmer
Gun nut
Harry Palmer's Avatar
Join Date: Oct 2003
Location: War Zone
Posts: 14,788
Harry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond repute
http://office.microsoft.com/en-us/ex...003056108.aspx

?
__________________
Plus ça change, plus c’est la même chose.

Reminder: "Do not look into laser with remaining good eye."
Harry Palmer is offline  
 Lupins 
Reply With Quote
Old 11-Sep-2012, 15:16   #68
BFH
Fixer of dead hard drives
BFH's Avatar
Join Date: Oct 2003
Location: The Final Frontier
Posts: 3,820
BFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond repute
A quick google produces...

"Assuming start time is in cell A1 and stop time is in cell B1, this formula will calculate total minutes.

=HOUR(B1-A1)*60+MINUTE(B1-A1)
"

So 60mins x 4 hours..... if else blah blah...

Why is it so hard?

Or am i missing something?
__________________
Elite Dangerous Current ship: Imperial Clipper,Cmdr Naughtius Maximus,Moto: fuck it
The BFH: http://www.andystoolkit.co.uk/thebfh.php
BFH is offline  
Reply With Quote
Old 11-Sep-2012, 15:18   #69
BFH
Fixer of dead hard drives
BFH's Avatar
Join Date: Oct 2003
Location: The Final Frontier
Posts: 3,820
BFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond reputeBFH has a reputation beyond repute
Bah - yep HP's link gives more options for you.
__________________
Elite Dangerous Current ship: Imperial Clipper,Cmdr Naughtius Maximus,Moto: fuck it
The BFH: http://www.andystoolkit.co.uk/thebfh.php
BFH is offline  
Reply With Quote
Old 11-Sep-2012, 15:29   #70
MZhukov
I liek boobies
MZhukov's Avatar
Join Date: Oct 2003
Location: Essex, England
Tournaments Won: 1
Posts: 1,735
MZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond repute
I can calculate the differences between them no issues, it's seeing if time period a is between period b for a minimum of four hours :/
__________________
24 hours in a day, 24 beers in a case......
MZhukov is offline  
 Mzhukov123  Tetris Champion
Reply With Quote
Old 11-Sep-2012, 15:38   #71
Harry Palmer
Gun nut
Harry Palmer's Avatar
Join Date: Oct 2003
Location: War Zone
Posts: 14,788
Harry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond reputeHarry Palmer has a reputation beyond repute
Quote:
Originally Posted by MZhukov View Post
Excel woes. I have a head ache trying to work this out and require some trickery assistance.

I have two date/time fields say;

01/02/12 12:00am and 01/02/12 13:34pm

I need to work out, over these two fields whether there is a four hour (or more) period between 00:00 and 08:00 and bring back a postive response. So in this example it would be where as;

01/02/12 06:30am and 01/02/12 11:30am doesn't

Can any one assist?
Check if period from first time -> 08:00 is > 4, if so then check period of second time -> 08:00 if negative then answer is true, if positive then calc diff between check 1 and check 2.
__________________
Plus ça change, plus c’est la même chose.

Reminder: "Do not look into laser with remaining good eye."
Harry Palmer is offline  
 Lupins 
Reply With Quote
Old 11-Sep-2012, 19:04   #72
Q-Target
AKA QuebecTango
Q-Target's Avatar
Join Date: Oct 2003
Location: Not Brighton
Posts: 36,945
Q-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond repute
^ that's as far as I got at work before I had to down tools.
Q-Target is online now  
Reply With Quote
Old 12-Sep-2012, 14:31   #73
MZhukov
I liek boobies
MZhukov's Avatar
Join Date: Oct 2003
Location: Essex, England
Tournaments Won: 1
Posts: 1,735
MZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond reputeMZhukov has a reputation beyond repute
After a couple of hours of head banging I cracked it with using auto filters and a random forumla
__________________
24 hours in a day, 24 beers in a case......
MZhukov is offline  
 Mzhukov123  Tetris Champion
Reply With Quote
Old 12-Sep-2012, 14:41   #74
[n00b]Zippy
Waterborne Trailer-Trash
[n00b]Zippy's Avatar
Join Date: Oct 2003
Location: Shropshire Union Canal
Posts: 13,057
[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute[n00b]Zippy has a reputation beyond repute
Hurray for forumlas!
__________________
And now we rise and we are everywhere
[n00b]Zippy is online now  
Send a message via MSN to [n00b]Zippy
Reply With Quote
Old 20-Sep-2012, 09:13   #75
Scrobbs
Sunnyvale Supervisor
Scrobbs's Avatar
Join Date: Oct 2003
Location: In the pipe, five by five.
Posts: 16,580
Scrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond repute
Dunno if this lot will be of any use - some stuff I've put together for data manipulation in individual cells for the most part, might be useful for some folk:


To remove all chars after a colon:
=LEFT(A1,FIND(":",A1)-1)

count unique values:
=SUM(IF(FREQUENCY(A1:A569,A1:A569)>0,1))

Extract text from cell:
=MID(D134,(FIND("http",D134)),((LEN(D134)-(FIND("http",D134))))+1)

Find value in cell and return FALSE if it's there:
=ISERR(FIND("SSL",H1))

Remove duplicates in a single cell:

In cell:

=RmvDupe(A1, ",")

VB:

Function RmvDupe(txt As String, Optional delim As String = " ") As String
Dim e
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each e In Split(txt, delim)
If Trim(e) <> "" And Not .exists(Trim(e)) Then .add Trim(e), Nothing
Next
If .Count > 0 Then RmvDupe = Join(.keys, delim)
End With
End Function
__________________
http://bit.ly/debatethebill

Last edited by Scrobbs; 20-Sep-2012 at 13:32.
Scrobbs is offline  
Reply With Quote
Old 20-Sep-2012, 09:32   #76
Bilb
Amazeballs!
Bilb's Avatar
Join Date: Oct 2003
Location: Elite Elevated Equine Division
Posts: 11,283
Bilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond reputeBilb has a reputation beyond repute
Proxy logs?
__________________
@TomJJarvis
Bilb is offline  
Send a message via Skype™ to Bilb  jarvthelegend 
Reply With Quote
Old 20-Sep-2012, 09:43   #77
Scrobbs
Sunnyvale Supervisor
Scrobbs's Avatar
Join Date: Oct 2003
Location: In the pipe, five by five.
Posts: 16,580
Scrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond repute
Nah. Results cleaning for reports.
__________________
http://bit.ly/debatethebill
Scrobbs is offline  
Reply With Quote
Old 20-Sep-2012, 13:00   #78
EvilGrin
my other forum is full
EvilGrin's Avatar
Join Date: Oct 2003
Location: Lancs, UK.
Posts: 8,194
EvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond repute
Just couple of ones I came across today.

If you are using pivot charts with a filter and you want to dynamically change the chart title based on what the filter says you can't just type a formula into the chart title and expect it to work. The chart title will however accept a direct cell reference. So you can do =A1 as the chart title text and that will work. In my specific case I had to append some text to the title to make it readable so I referenced another empty cell in the worksheet and did a CONCATENATE of a static string and the active filter text there.

Then I had to cut and paste these charts into Outlook and mail them off to one of our sales guys. Make sure you use 'Paste -> Picture' otherwise Outlook links to the chart data source and every time you alter the filter in the pivotchart you change the embedded charts in your email!
__________________
Edugeek - Techies in Education!
EvilGrin is offline  
Send a message via ICQ to EvilGrin Send a message via AIM to EvilGrin Send a message via MSN to EvilGrin Send a message via Yahoo to EvilGrin  EvilGrinUK 
Reply With Quote
Old 25-Oct-2012, 09:36   #79
EvilGrin
my other forum is full
EvilGrin's Avatar
Join Date: Oct 2003
Location: Lancs, UK.
Posts: 8,194
EvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond repute
Here's a fun one. I have a list of sales reps and the postcode of their base of operations. I also have a much bigger list of records to assign to them. I need to do this based on the nearest rep geographically. Any ideas?
__________________
Edugeek - Techies in Education!
EvilGrin is offline  
Send a message via ICQ to EvilGrin Send a message via AIM to EvilGrin Send a message via MSN to EvilGrin Send a message via Yahoo to EvilGrin  EvilGrinUK 
Reply With Quote
Old 25-Oct-2012, 09:53   #80
qui
Iron Man 3
qui's Avatar
Join Date: Oct 2003
Location: Uk
Posts: 4,128
qui has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond repute
Is 'as the crow flies' distance acceptable?
__________________
i-rox - rtfm - yoga - dvorak - qupp - timmeh - japan & nz - marik
qui is offline  
 digitalKwee 
Reply With Quote
Old 25-Oct-2012, 09:57   #81
qui
Iron Man 3
qui's Avatar
Join Date: Oct 2003
Location: Uk
Posts: 4,128
qui has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond reputequi has a reputation beyond repute
http://ukpostcodes.tenfourzero.net/

http://www.postcode.org.uk/

http://www.movable-type.co.uk/scripts/latlong.html

http://www.cpearson.com/excel/LatLong.aspx

http://www.geonames.org/

That's a list of the urls I acquired when I was working on post code stuff.
__________________
i-rox - rtfm - yoga - dvorak - qupp - timmeh - japan & nz - marik
qui is offline  
 digitalKwee 
Reply With Quote
Old 25-Oct-2012, 11:12   #82
EvilGrin
my other forum is full
EvilGrin's Avatar
Join Date: Oct 2003
Location: Lancs, UK.
Posts: 8,194
EvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond repute
Quote:
Originally Posted by qui View Post
Is 'as the crow flies' distance acceptable?
Yeah that's fine.
__________________
Edugeek - Techies in Education!
EvilGrin is offline  
Send a message via ICQ to EvilGrin Send a message via AIM to EvilGrin Send a message via MSN to EvilGrin Send a message via Yahoo to EvilGrin  EvilGrinUK 
Reply With Quote
Old 25-Oct-2012, 11:16   #83
Q-Target
AKA QuebecTango
Q-Target's Avatar
Join Date: Oct 2003
Location: Not Brighton
Posts: 36,945
Q-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond repute
This:
http://www.businesslistsuk.com/image..._map_large.gif
and this:
http://www.theofficedealer.com/mm5/g...tric-Ruler.jpg

HTH
Q-Target is online now  
Reply With Quote
Old 25-Oct-2012, 11:21   #84
EvilGrin
my other forum is full
EvilGrin's Avatar
Join Date: Oct 2003
Location: Lancs, UK.
Posts: 8,194
EvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond repute
I have 2200 records and 108 reps. I need to do this systematically.
__________________
Edugeek - Techies in Education!
EvilGrin is offline  
Send a message via ICQ to EvilGrin Send a message via AIM to EvilGrin Send a message via MSN to EvilGrin Send a message via Yahoo to EvilGrin  EvilGrinUK 
Reply With Quote
Old 25-Oct-2012, 11:23   #85
Q-Target
AKA QuebecTango
Q-Target's Avatar
Join Date: Oct 2003
Location: Not Brighton
Posts: 36,945
Q-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond repute
I recall doing something like this at Uni many moons ago. I have absolutely no recollection of how we went about it but some of the content of this wiki page seems familiar...

http://en.wikipedia.org/wiki/Shortest_path_problem
Q-Target is online now  
Reply With Quote
Old 25-Oct-2012, 11:24   #86
Q-Target
AKA QuebecTango
Q-Target's Avatar
Join Date: Oct 2003
Location: Not Brighton
Posts: 36,945
Q-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond repute
Actually, for your problem, that's probably utterly useless. Sorry.
Q-Target is online now  
Reply With Quote
Old 25-Oct-2012, 11:26   #87
EvilGrin
my other forum is full
EvilGrin's Avatar
Join Date: Oct 2003
Location: Lancs, UK.
Posts: 8,194
EvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond repute
Hrum, looks like I'll need to convert all the postcodes to lat/long then do shortest path for all of them (that's 237k calculations), then pick the lowest result. I suspect this is going to make excel very sad.
__________________
Edugeek - Techies in Education!
EvilGrin is offline  
Send a message via ICQ to EvilGrin Send a message via AIM to EvilGrin Send a message via MSN to EvilGrin Send a message via Yahoo to EvilGrin  EvilGrinUK 
Reply With Quote
Old 29-Oct-2012, 11:52   #88
EvilGrin
my other forum is full
EvilGrin's Avatar
Join Date: Oct 2003
Location: Lancs, UK.
Posts: 8,194
EvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond reputeEvilGrin has a reputation beyond repute
Indeed it did!

I stole this VBA code from here to work out the lat/long and the drive times (It basically asks google).

http://oco-carbon.com/2012/03/06/goo...ance-function/

Then once I had a massive spreadsheet of drive times arranged by site vs record. I used the following to assign the sites to records:

Code:
=INDEX($C$1:$DF$1,MATCH(MIN(C3:DF3),C3:DF3,0))
It took 7 hours for Excel to work this all out. Mainly due to the delay I had to put into the google lookup to stop it timing me out.

Also related to the above I made number of records from site in 5 mile bandings. You can do this by using IFCOUNT and pivoting the results.
__________________
Edugeek - Techies in Education!

Last edited by EvilGrin; 29-Oct-2012 at 11:55.
EvilGrin is offline  
Send a message via ICQ to EvilGrin Send a message via AIM to EvilGrin Send a message via MSN to EvilGrin Send a message via Yahoo to EvilGrin  EvilGrinUK 
Reply With Quote
Old 29-Oct-2012, 11:55   #89
Q-Target
AKA QuebecTango
Q-Target's Avatar
Join Date: Oct 2003
Location: Not Brighton
Posts: 36,945
Q-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond reputeQ-Target has a reputation beyond repute
... and people will not be anywhere near as impressed by this system as you are.
Q-Target is online now  
Reply With Quote
Old 29-Oct-2012, 12:38   #90
Scrobbs
Sunnyvale Supervisor
Scrobbs's Avatar
Join Date: Oct 2003
Location: In the pipe, five by five.
Posts: 16,580
Scrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond reputeScrobbs has a reputation beyond repute
I'm impressed tbh. Good skills.
__________________
http://bit.ly/debatethebill
Scrobbs is offline  
Reply With Quote
Reply

Go Back   trickery.net > General > Discussion

Tags
excel solutions

Similar Threads
Thread Thread Starter Forum Replies Last Post
The Perpetual Thread of Rant! Bilb Discussion 24305 15-Oct-2018 15:32
The Perpetual Thread of Praise! ReichSteadyCrew Discussion 13745 02-Oct-2018 12:08
Perpetual thread of "An interesting/bizarre thing I saw today" Q-Target Discussion 32 17-Aug-2008 23:44
Thread for n00b questions with simple answers Q-Target Software & Security 33 23-Dec-2007 12:35
BF2 FAQ / Questions Thread? andy Battlefield Series 9 14-Jun-2005 20:24

Users Viewing Thread: 1 (0 members and 1 guests)
 

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 17:58.


Powered by vBulletin® Version 3.7.0 Release Candidate 3
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright ©2003 - 2013, trickery.net