Mister Ian's Weblog

Author: Ian

  • Excel Reverse Pivot Table

    Excel VBA to reverse a Pivot Table. You can always trust finding Excel stuff if you use J-Walk as part of the search.

    Sub ReversePivotTable()
  ' Before running this, make sure you have a summary table with column headers.
  ' The output table will have three columns.
  Dim SummaryTable As Range, OutputRange As Range
  Dim OutRow As Long
  Dim r As Long, c As Long
  On Error Resume Next
  Set SummaryTable = ActiveCell.CurrentRegion
  If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
    MsgBox "Select a cell within the summary table.", vbCritical
    Exit Sub
  End If
  SummaryTable.Select
  Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
  ' Convert the range
  OutRow = 2
  Application.ScreenUpdating = False
  OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
  For r = 2 To SummaryTable.Rows.Count
    For c = 2 To SummaryTable.Columns.Count
      OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
      OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
      OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
      OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
      OutRow = OutRow + 1
    Next c
  Next r
End Sub

  • What’s In a Website

    A website is made of many components.

    1. Servers and software. You can host it in many ways. WordPress is a CMS and you can put onto WordPress.com
    2. Site setup, logins etc. Different levels can be setup: Admin, editor etc.
    3. Site layout, types of pages and posts, templated.
    4. Site behaviour, hover, parallax etc.
    5. Colours, backgrounds, borders, rounded edges, spacing, drop shadows etc.
    6. Fonts and font sizes
    7. Borders
    8. Site content: Images, icons, and text editing.

  • 100 Years Ago Passchendaele

    This isn’t my Grandfather Frank Clifford Butterworth but he was wounded exactly 100 years ago in the Battle of Passchendaele.

    The commitment to service is strikingly shown in the form below: “Terms of Service: Duration of war”

    He was wounded in his face, shoulder and leg.

    • His shoulder wound severed a major artery. The records show he received a litre of blood.
    • He never gained normal function of his arm.
    • His facial would was severe enough to later cause an infected parotid gland (the salivary gland).

    He ended up in a hospital in England and was “invalided” to Canada October 18, 2018 for further months of recovery.

    Note if you are interested you can access Personnel Records of the First World War here.

  • Farm Animals

    My favourite cat!

    The boss (rooster), making noise

    And a colourful chicken.

  • Canmore Folk Festival Bruce Cockburn

    40th Anniversary of the Canmore Folk Music Festival. It rained but ended well before the Blackie and the Rodeo King’s rocking set followed up by Bruce Cockburn’s performance which ended too soon. He did some of it with Colin Linden of Black and the Rodeo Kings. They have fun performing together! Colin was producer of the new album coming out mid September which I’m eagerly awaiting.

    Here’s a recent nice short interview with him.

  • Thumb recovery

    This blog needs a post now and again. So here’s one. I’m one week into wearing the brace. One more week to go and then a couple of weeks part time. It can’t go any faster.

    This is my x-ray analysis.

    This is my x-ray.

    And this is the x-ray example on the Wikipedia page about Gamekeeper’s Thumb.


    By James Heilman, MD – Own work, CC BY-SA 3.0, Link

    The blue rubber band on the thumb is to hold the velcro on as it isn’t holding well as the material has stretched.

    The sad part is no bicycling for a while. And, the bill just arrived, from Alberta Health Services. Rockyview General Hospital – Wrist Splint 1 @ $35.00.