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
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
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) & “”
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
ws.AutoFilterMode = False
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.

Parse RSS feed with Google feed api

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



<!–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
//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
//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
//append the entry title as a chiled of the anchor tag
//finish by ending the ul tag inside the container

<div id=”feed”></div>

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

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



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.

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 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.

Browser support for Ogg Vorbis

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