Cross Join Columns in Excel

As part of a project I’m working on I need to create a cross join of users against symbols; the idea being that any of the given users might look at any of the given symbols. I have a list of users and a list of symbols which I essentially need a cross-join on.

For this example I’ll use cars and engine/transmission variants.

Lists

From these two lists I need to quickly create all possible combinations;

cross-joined lists

I can’t find any function that will do what I was so I’ve created a VBA function that will do it;

Function CrossJoin(r1 As Range, r2 As Range) As Variant
    Dim size As Integer
    Dim arr As Variant
    Dim offset As Integer
    size = r1.Rows.Count * r2.Rows.Count
    ReDim arr(0 To size, 0 To 1)
    offset = 0

    For i = 1 To r1.Rows.Count
        For j = 1 To r2.Rows.Count
            arr(offset, 0) = r1.Cells(i, 1).Value2
            arr(offset, 1) = r2.Cells(j, 1).Value2
            offset = offset + 1
        Next j
    Next i

    CrossJoin = arr
End Function

To use the function select and empty cell and insert =CROSSJOIN(A1:A4, B1:B4) (adjusting the input range as required) then when the function has evaluated extend the range to the size of the expected cross join. (in this case 16x2).

To calculate the array function hit F2 then press Ctrl+Shift+Enter to calculate the array.


Using yum history

I have been setting up a couple of Red Hat boxes and I need to have a cron task to mount a network share from an AD domain, copy the files then dismount.

In the past I have successfully accessed the network shares using a given set of credentials but I could not get it to work after install samba-client and samba-common.

My script on the new machine was the same as the script on the working machine so there had to be a discrepency on installed packages, step in yum history.

To get the history of yum commands use

yum history

which will give the output similar to;

yum history output

Thankfully, from the dates I was able to work out the point that I had done the install. From this point you can look at a specific item, for example;

yum history info 57

which will return more information about the command

yum history info for id 57


Office365 and SharePoint iPad Friendly

I have been doing some work for a company who have a user base who primarily use iPads to access the SharePoint site. There was a requirement to allow those users to easily access the team site without getting their screens filled with the quick launch and the bloated title.

Initially, I solved this using by using JavaScript to set the page to full screen;

window.onload = function () {

    //alert('has loaded');
    SP.SOD.executeOrDelayUntilScriptLoaded(goToFullScreen, 'sp.js');
    // For use within normal web clients
    function goToFullScreen() {
    	//alert('goToFullScreen');
    	var isiPad = navigator.userAgent.indexOf('iPad') != -1;
    	if(isiPad) {
    		setTimeout(goFullScreen, 500);
    	}
    }

    function goFullScreen() {
    	SetFullScreenMode(true);
    }

    SP.SOD.notifyScriptLoadedAndExecuteWaitingJobs("sp.js");

};

The problem with this approach was the short delay from the page loading to the screen resizing to remove the side bar etc which was undesirable.

I was asked to find a better way to remove the menus that was more instantaneous so I turned to css media queries.

To achieve this, I looked at what the SharePoint SetFullScreenMode function actually did. I found that in addition to setting the cookie, it also applies the ms-fullscreenmode css class to the body element, which led me to the following CSS in the master page;

<style>

	@media only screen and (min-device-width: 768px) and (max-device-width: 1024px){

		#navresizerVerticalBar{
			display:none;
		}
		#navresizerHorizontalBar{
			display:none;
		}
		#s4-titlerow {
			display:none !important;
		}
		#sideNavBox {
			display:none;
		}
		#contentBox {
			margin-left:40px;
		}
		#contentBox {
			margin-left:0px;
		}
	}
</style>

TMA470 first assignment returned

I got the result and feedback for the first assignment of the final Open University project today. Its been a long week waiting for it, not least because a significant number of others on the course have been reporting back very low marks.

The difference with TM470 assignments is that they are a little more wooly in what is required. For the past 3 years, the assignments have been a series of questions that need answering in a specified order and within a specified structure. With this course we’re expected to guide the project, determine what is relevant for the content of the assignment and hope that we got it right.

I eventually got my mark, 60%. It’s work 5% of the overall module so it can be used as a learning experience. The tutor did give me some good feedback which will help when writing the next assignment for the middle of April.


OU Final project finally started

At the eleventh hour I finally settled on the project that I am going to be working on for the next 8 months. I had previously intended to work on a project that would allow me to play with some javascript graph layout and visualisation libraries. Unfortunately this intention didn’t have enough of a clear direction to make it a solid proposition for a successful project.

So, the project I’m working on is based on my dad’s job in modern tram systems. The focus of the project will be to provide a software library that can be used to assist in the validation of chose routes for a tram system. Basically a route is made up of a series of segments that can be straight, curved or a transition from straight to curved. Along the route there may be platforms, crossing, traffic lights etc but essentially these three segment types cover most of it.

When a tram is travelling through a curve there are a number of factors which dictate the speed the tram can travel. Sections of track may have environmental factors which dictate a maximum speed (eg; an area of high pedestrian traffic). The library will seek to provide a journey time for each type of tram through the route by calculating the permissible speed through each segment of the route.

I’ll update as I go, you might think its a dry subject, however the calculation logic should make for some interesting challenges.