RaeAllen.net

Category: development

Items related to my work as a development lead

  • Split data into multiple Excel worksheets based on column with VBA code

    I regularly get a block of data with 45 different entries in the first collum, repeated for each time a month appears in column 2. I wanted to split the data based on column value quickly and automatically

    The following VBA code from extendoffice.com worked well

    1. Open the Microsoft Visual Basic for Applications window. I used the Developer > Visual Basic commands from the toolbar

    2. Click Insert > Module, and paste the following code in the Module Window.

    Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    vcol = 1
    Set ws = Sheets(“Sheet1”)
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = “A1:C1”
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = “Unique”
    For i = 2 To lr
    On Error Resume Next
    If ws.Cells(i, vcol) “” And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
    ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
    End If
    Next
    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear
    For i = 2 To UBound(myarr)
    ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & “”
    If Not Evaluate(“=ISREF(‘” & myarr(i) & “‘!A1)”) Then
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & “”
    Else
    Sheets(myarr(i) & “”).Move after:=Worksheets(Worksheets.Count)
    End If
    ws.Range(“A” & titlerow & “:A” & lr).EntireRow.Copy Sheets(myarr(i) & “”).Range(“A1”)
    Sheets(myarr(i) & “”).Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    End Sub

    Note: In the above code:

    vcol =1, the number 1 is the column number that you want to split the data based on.
    Set ws = Sheets(“Sheet1”), Sheet1 is the sheet name that you want to apply this code.
    title = “A1:C1”, A1:C1 is the range of the title.
    All of them are variables, you can change them as your need.

    3. Then press F5 key to run the code, all data in the active worksheet are split into multiple worksheets by the column value. And the split worksheets are named with the split cell names.

    Note: The split worksheets are placed in the end of the workbook where the master worksheet is in.

  • Spreadsheet formulas: Prepend by concatenation

    Have had to delve into some formulas for processing text files so needed someplace to jot them down

    Prepend the word GET and a space to front of a string by the concatenation method
    =CONCATENATE(“GET “,B1)

  • Parse RSS feed with Google feed api

    This is a fully commented method parsing a mediarss feed using the now deprecated Google Feed api

    litn

    http://www.abc.net.au/landline/test/news-rss/

    <html>
    <head>
    <!–load the google javascript api – now depricated–>
    <script type=”text/javascript” src=”https://www.google.com/jsapi”></script>
    <script type=”text/javascript”>

    google.load(“feeds”, “1”);

    function initialize() {
    //load the rss feed
    var feed = new google.feeds.Feed(“http://www.abc.net.au/news/feed/7234284/rss.xml”);
    //set the number of entreies you want from the feed
    feed.setNumEntries(10);
    //load the results
    feed.load(function(result) {
    if (!result.error) {
    //look for the container div
    var container = document.getElementById(“feed”);
    //inside the container div creat a ul element
    var ul = document.createElement(“ul”);
    for (var i = 0; i < result.feed.entries.length; i++) {
    var entry = result.feed.entries[i];
    var li = document.createElement(‘li’);
    //for each item, appent an li element to the ul
    ul.appendChild(li);
    //creat and anchor element
    var a = document.createElement(‘a’);
    //give the anchor element the href attribute and the value of the entry link
    a.setAttribute(“href”, (entry.link));
    //append that anchor as a child of the li element
    li.appendChild(a);
    //append the entry title as a chiled of the anchor tag
    a.appendChild(document.createTextNode(entry.title));
    //finish by ending the ul tag inside the container
    container.appendChild(ul);
    }
    }
    });
    }
    google.setOnLoadCallback(initialize);

    </script>
    </head>
    <body>
    <div id=”feed”></div>
    </body>
    </html>

  • Testing Tunein embed

    seeing if the 612 stream will work as an embed

  • Spreadsheet formulas: Remove characters from the right

    Have had to delve into some formulas for processing text files so needed someplace to jot them down

    Remove 11 characters from the right of the string in a cell:
    =RIGHT(A1, LEN(A1)-11)

    How does this work?

    Le’s say you have a number of locations with Australia at the start, e.g. Australia: Brisbane, Australia: Sydney

    • LEN(A1) returns the length of the string in cell A1: 19 letters in Australia: Brisbane
    • It then subtracts 11, in order to leave out the first 11 characters: Australia plus the colon plus the space
    • Then RIGHT() takes the last 8 letters from the string and returns: Brisbane
    • In effect, this has removed the first 11 characters of the string

  • Toxins in the home

    Surprise Toxins In Your Home infographic by Column Five Media.

  • Data Journalism

    Links used in recent lecture

    • Definition: wikipedia
      • Find: Searching for data on the web
      • Clean: Process to filter and transform data, preparation for visualization
      • Visualize: Displaying the pattern, either as a static or animated visual
      • Publish: Integrating the visuals, attaching data to stories
      • Distribute: Enabling access on a variety of devices, such as the web, tablets and mobile
      • Measure: Tracking usage of data stories over time and across the spectrum of uses.
    • The Guardian
    • data visualisation

    Sources

    Examples

  • Using SSI to select a day

    You can use SSI to select a specific day, and then carry out an action. You can also determine that action to be carried out at a certain time on that day.

    1. First configure the time format to the day of the year
      <!–#config timefmt=”%j” –>
    2. Then use an SSI to select a specific day
      <!–#if expr=”($DATE_LOCAL = /75/)” –>
      This will select the 15 March in 2012
    3.  You can use SSI to select a number of days.
      <!–#if expr=”($DATE_LOCAL = /75/)||($DATE_LOCAL = /76/)||($DATE_LOCAL = /77/)||($DATE_LOCAL = /78/)||($DATE_LOCAL = /79/)||($DATE_LOCAL = /80/)||($DATE_LOCAL = /81/)” –>
      This will select from day 75 through to day 81
    4. You can also select a specific time within this day
      <!–#if expr=”${DATE_LOCAL} = /296/ “–>
      <!–#if expr=”((${DATE_LOCAL} > 2961659) && (${DATE_LOCAL} < 2961901)) “–>
      This will look at day number 296, and then carry out the command for the time from 1659 local time to 1901 local time based on the server.

  • Scooter parking near Southbank

    Scooter and motorcycle parking within 1 km of new ABC Southbank offices


    View Scooter and Motorcycle parking in a larger map

  • HTML5 audio

    MP3 source file

    <audio src="/audio/straw bales.mp3" controls="controls" preload="metadata"></audio>
    This will show the audio and the controls but won’t autoplay or loop. It will preload the metadata only however some browsers may not take notice of this restriction.
    /audio/straw bales.mp3

    <audio src="/audio/straw bales.mp3" controls preload="metadata" autoplay loop></audio>
    This will add the autoplay and loop parameters to play the item on load and loop the audio from the end.

    Browser Support for MP3 audio
    Support for the MP3 format audio is restricted to Safari, IE 9 onwards, and Chrome.

    Ogg source file
    <audio src="/audio/straw bales.ogg" controls="controls" preload="metadata"></audio>
    This will show the audio and the controls but won’t autoplay or loop. It will preload the metadata only however some browsers may not take notice of this restriction.
    /audio/straw bales.ogg

    Browser support for Ogg Vorbis

    Support for the Ogg Vorbis format audio is restricted to Firefox, Chrome and Opera