Adventures in Excel. and where OCD can get you.
Jul 20, 2019 14:06:12 GMT -5
squirejames88 likes this
Post by efanton on Jul 20, 2019 14:06:12 GMT -5
Im sure at this stage you have, like myself, all tried many many different URL's with no success.
I have tried so many that I started to loose track of what I had done and what I had yet to try so decided I needed a way to keep track.
A spreadsheet to keep track, that will be a simple solution wouldn't it?, well maybe not. Read on if you're curious as to where obsessive behavior can take you.
.
As is normal in my life I got a bit OCD , but the result of 3 nights solid and very little sleep I think I have come up with something truly impressive.
So I started with three columns, representing the three parts of a web address (stanza3,stanza4.stanza5). In each columns I was keeping track of everything I had tried but after a while I realised that there could be endless combinations.
I decided that excel was going to do this for me, but didn't have a clue how to go about it.
Much reading, experimenting, failure and hair-pulling later, I got it to work
You put this in the top cell of the 4th column and copy it down.
=IFERROR(INDEX($A:$A,IF(INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2>COUNTA(A:A),-1,INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2))&"."&INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(C:C)-1)),(COUNTA(B:B)-1))+2)&"."&INDEX(C:C,MOD((ROW(1:1)-1),(COUNTA(C:C)-1))+2),"")
WARNING this list gets extremely long if you have tried as many options as me and it will take excel a LONG LONG time to calculate all options.
Delighted with the results, but daunted by the prospect of typing in and trying every single one of them individually, (all 1200 at my first attempt) I realized that even my OCD was not up to the task and I had to find a better solution.
So more reading, research, and hair-pulling until I found a way to convert next column to convert the results in URLs into the correct format (HTTP://xxx.yyy.zzz)
It was quite simple in the end you just use this in the top cell of the next column
=IF(D1="","","http://"&D1)
Initially I converted all these to a hyperlink using the HYPERLINK function but then realised that I would still have to click every single entry in the column to try open the page in a browser. Bad mistake. Most of them will not even open a webpage.
So more reading, research, and hair-pulling to see if there was a way to see if excel could check whether these were or were not valid web links.
No such function in excel exists , but I was on a mission and my OCD took hold again. I had read somewhere that you can create your own functions in excel. I have very little programming experience, but how hard could that be, its got to be simple hasn't it
Any how 12 hours more straight, much reading, research, and hair-pulling I read that you can create function in excel using VBA.
The next night I sat down thinking, I have a rough idea of what to do, I've done the research, it really cant be that hard
What I came up with is this.
Public Function CheckURL(url As String)
Dim request As Object
Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
On Error GoTo haveError
With request
.Open "HEAD", url
.Send
CheckURL = .Status
End With
Exit Function
haveError:
CheckURL = Err.Description
End Function
Dim request As Object
Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
On Error GoTo haveError
With request
.Open "HEAD", url
.Send
CheckURL = .Status
End With
Exit Function
haveError:
CheckURL = Err.Description
End Function
What it does is essentially allow you to check a cell for URL validity, you use it like this CheckURL(D3) if cell D3 contains a URL, and it returns the URL Status code. You might not think yo know what a URL status code is, but you see it all the time. Enter a wrong web address and we have all seen the dreaded 404 error. That is a URL status code. URL status code 200 means that the URL exists and can be accessed.
So in the top cell of the next column i entered the following
=IF(E1="","",CheckURL(E1))
I now have a column besides each URL telling me its status code and know exactly which entries will give a valid webpage. In the next column I do a simple check to see which cells return a value of 200, then lookup the corresponding URL and put this in the appropriate cell using a HYPERLINK function.
I now have a column (a very very long column) that has sporadically down through it, a list of hyperlinks. Splitting my screen so that I can see both my browser and the spreadsheet, I simply click each hyperlink in the spreadsheet and that link opens in the browser. How cool is that
When you click the link the hyperlink changes colour so that you know the ones you have already clicked.
All I had to do then was look at the webpages that were opened, and check to see whether they were the buried treasure webpage. Many many click later, and no I have not found buried treasure site
The great thing about this is that I now have a list of weblinks That I have already tried or are yet to be tried.
If I want to try new combination I simply add my guesses for stanza 3, 4, and 5 and force excel to recalculate, watch excel going crazy for a hour or so, and the look to see if it produces any new fresh links that have yet to be clicked.
Cant post the spreadsheet its simply to big, but if you too are mad enough or OCD enough to try it the above you walks you through it.
Hope you all got a bit of a giggle, I can laugh too now that its done.
I have decided that if I win this treasure hunt, I will put the €1000 towards a desperately needed hair transplant